Thread: plpgsql -- arrays/temporary tables?

The following function doesn't work when called multiple times:

CREATE FUNCTION foo(INTEGER) RETURN BOOLEAN AS '
     DECLARE
         y INTEGER;
     BEGIN
         CREATE TEMP TABLE a (
             x INTEGER
         );
         INSERT    INTO a
         VALUES    (4);
         SELECT    INTO y x
         FROM      a;
         DROP TABLE a;
         RETURN TRUE;
     END;
' LANGUAGE 'plpgsql';

I understand that this is because the query plan uses the existing table
`a' repeatedly.

My questions:

* How can I make this work?  Are temporary tables essentially useless
inside plpgsql functions?
* Does plpgsql support array constructs?  How can I use one of these?  How
can I use it in an IN clause?  (Assume the array contained a list of
primary keys.)
* I've considered trying plperl, but I've seen no examples anywhere of how
to execute SQL queries inside plperl!  Any pointers on documentation, or an
example, or anything?  I've combed the search engines for a clue but
haven't found one.

I am using 7.1RC4.  Thanks in advance for any tips!


--
Steven D. Arnold          Que quiero sera         stevena@neosynapse.net
"He was part of my dream, of course -- but then  I was part of his dream
too."                                                   -- Lewis Carroll

hi
I think create likeddl statements fal under the category of dynamic
queries which plpgsql in its native state cant handl..

Try using EXECUTE to execute this query

Hope this helps
Anand
On Wed, Apr 11, 2001 at 12:25:43AM -0400, Steven D. Arnold wrote:
>The following function doesn't work when called multiple times:
>
>CREATE FUNCTION foo(INTEGER) RETURN BOOLEAN AS '
>     DECLARE
>         y INTEGER;
>     BEGIN
>         CREATE TEMP TABLE a (
>             x INTEGER
>         );
>         INSERT    INTO a
>         VALUES    (4);
>         SELECT    INTO y x
>         FROM      a;
>         DROP TABLE a;
>         RETURN TRUE;
>     END;
>' LANGUAGE 'plpgsql';
>
>I understand that this is because the query plan uses the existing table
>`a' repeatedly.
>
>My questions:
>
>* How can I make this work?  Are temporary tables essentially useless
>inside plpgsql functions?
>* Does plpgsql support array constructs?  How can I use one of these?  How
>can I use it in an IN clause?  (Assume the array contained a list of
>primary keys.)
>* I've considered trying plperl, but I've seen no examples anywhere of how
>to execute SQL queries inside plperl!  Any pointers on documentation, or an
>example, or anything?  I've combed the search engines for a clue but
>haven't found one.
>
>I am using 7.1RC4.  Thanks in advance for any tips!
>
>
>--
>Steven D. Arnold          Que quiero sera         stevena@neosynapse.net
>"He was part of my dream, of course -- but then  I was part of his dream
>too."                                                   -- Lewis Carroll
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html