Re: table name as parameter in pl/psql

On Mon, 10 Dec 2001, [utf-8] darthxiong@libero.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';