Re: table name as parameter in pl/psql

On Mon, 10 Dec 2001, [utf-8] darthxiong(at)libero(dot)it wrote:

>
> hi all, i'm trying to do something like this:
>
> CREATE FUNCTION read_table(text) RETURNS int AS '
> DECLARE
> table_name ALIAS FOR $1;
> res
> INTERGER;
> BEGIN
> SELECT INTO res COUNT(id) FROM table_name;
> RETURN res;
> END;
> ' LANGUAGE 'plpgsql';
>
> using psql the creation return no errors, but the statement
> SELECT read_table( 'books' ) AS how_many;
> resuts in
> ERROR: parser: parse error at or near "$1"
>
> and the same using
> SELECT INTO res COUNT(id) FROM $1;
> instead of
> SELECT INTO res COUNT(id) FROM table_name;
> while
> SELECT INTO res COUNT(id) FROM books ( the real name of the table )
> works good

You need to look into using EXECUTE if you want to specify tables
on the fly, and you may need something like:
create function read_table(text) returns int as '
declare
table_name alias for $1;
rec record;
begin
for rec in EXECUTE ''select count(*) from '' || table_name LOOP
return rec.count;
END LOOP;
return 0;
end;' language 'plpgsql';