Re: table name in pl/pgsql

Adam Witney wrote:
> I think you would have to do it something like this, although whether the
> SELECT INTO works in an EXECUTE context I am not sure (note, completely
> untested code!)
>
> CREATE FUNCTION get_count(text, text) RETURNS int2 AS '
> DECLARE
>   cnt int4;
> BEGIN
>
>   EXECUTE ''SELECT INTO cnt COUNT(*) FROM table_'' || $1 || '' WHERE key =
> '' || $2;

That won't work either, you'll need to user FOR..IN..EXECUTE:

CREATE TABLE exectest (a integer, b text, PRIMARY KEY (a));
COPY exectest FROM stdin;
1   aaa
2   bbb
3   ccc
\.

CREATE FUNCTION demo_exec_fn() RETURNS boolean AS '
DECLARE
     r     RECORD;
BEGIN
     FOR r IN EXECUTE ''SELECT * FROM exectest''
     LOOP
         RAISE NOTICE ''a=%, b=%'', r.a, r.b;
     END LOOP;
     RETURN true;
END
' LANGUAGE plpgsql;

SELECT demo_exec_fn();

--
   Richard Huxton
   Archonet Ltd