Re: table name as parameter in pl/psql

Поиск
Список
Период
Сортировка
Искать

Re: table name as parameter in pl/psql

От:
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Дата:

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';

table name as parameter in pl/psql

От:
"darthxiong@libero.it" <darthxiong@libero.it>
Дата:

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
 
there is a way to use a table name as parameter? where am i wrong?
 thx 
Ivan
FAQ