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
Show quoted text
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?