plpgsql -- arrays/temporary tables?

From: "Steven D(dot) Arnold" <stevena(at)neosynapse(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: plpgsql -- arrays/temporary tables?
Date: 2001-04-11 04:25:43
Message-ID: 5.0.2.1.2.20010411002534.04701008@phear.dementian.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)neosynapse(dot)net
"He was part of my dream, of course -- but then I was part of his dream
too." -- Lewis Carroll

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew McMillan 2001-04-11 05:35:33 Re: Speaking of Indexing... (Text indexing)
Previous Message Steven D. Arnold 2001-04-11 03:35:53 plpgsql -- arrays/temporary tables?