Re: Table name as parameter in function

On Tue, 23 Nov 2004, Timothy Perrigo wrote:

> Sorry for the brief response earlier; I was a bit rushed. After
> looking into it, it's a bit messier than I thought (at least, as far as
> I can tell...perhaps one of the gurus on this list can show us a better
> way).
>
> Ordinarily, when you write select statements (for example) in a plpgsql
> function, it will attempt to cache the execution plan. In your case,
> though, you want to be able to hit different tables each time your
> function is invoked, so you need a way to construct and execute your
> query dynamically. That's where the EXECUTE statement comes in.
> EXECUTE allows you to issue a command that is prepared every time it is
> run.
>
> In your case, though, things are a bit trickier. There's no way to get
> the results of a dynamically executed select statement within a plpgsql
> function (according to the docs, the results are discarded). In your

Explain as a statement doesn't return results, but FOR recordvar IN
EXECUTE ... should work. It's still ugly, but something like

create or replace function count_rows(table_name text) returns integer
as
$$
declare
foo record;
begin
for foo in execute 'select count(*) as count from ' || quote_ident($1)
loop
return foo.count;
end loop;
end;
$$ language 'plpgsql';

should work for 8.0b. IIRC, at least 7.4 should work similarly if you
change the quoting.