plpgsql -- arrays/temporary tables?

plpgsql -- arrays/temporary tables?

Post by Steven D. Arnol » Thu, 12 Apr 2001 13:34:20



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!

--

"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

 
 
 

plpgsql -- arrays/temporary tables?

Post by Anand Ram » Thu, 12 Apr 2001 17:54:00


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


>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!

>--

>"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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

 
 
 

plpgsql -- arrays/temporary tables?

Post by Steven D. Arnol » Sun, 15 Apr 2001 10:22:08


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!

--

"He was part of my dream, of course -- but then  I was part of his dream
too."                                                   -- Lewis Carroll

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

1. plpgsql temporary table problem

Hi people,

I seem to have a problem with repeated temporary table creation in
a plpgsql function. The problem is illustrated below.

I'm running 7.2.1 on Linux. I should add that I just upgraded from 7.1.3
and
had no problems - congratulations to you all.  (I have no idea on
whether this
problem occurred on 7.1.3, its part of new code I've just written.)

The second "select parent_copy(...) " statement below gives the message:

NOTICE:  Error occurred while executing PL/pgSQL function parent_copy
NOTICE:  line 17 at SQL statement
ERROR:  Relation 2398261 does not exist

Cheers,
Geoff Russell

/*
 * sample illustrating either a "create temp table ... " problem
 * or perhaps just something I don't understand!
 */

/* first make some tables and data */
drop sequence parent_id_seq;
drop table parent;
drop table child;
create table parent (
     id serial,
     data integer
);
drop table child;
create table child (
     parent_id integer,
     data integer
);
insert into parent (data) values(1);
insert into child (parent_id,data) values(1,1);
insert into child (parent_id,data) values(1,2);
insert into child (parent_id,data) values(1,3);
/* now a pgsql function to copy children to a new parent  */
drop FUNCTION parent_copy (integer);
CREATE FUNCTION parent_copy (integer) RETURNS integer as '
DECLARE
     fromid alias for $1;
     newparentid integer;
BEGIN
     select into newparentid nextval(''parent_id_seq'');
     /* make the new parent and add it to table
      * (NOTE:
      *    it would be really nice to use a RECORD variable as follows.
      *    parentrecord RECORD;
      *    select into parentrecord select * from parent where
id=fromid;
      *    parentrecord.id=newparentid;
      *    insert into parent parentrecord;
      * )
      */
     insert into parent (id,data) values (newparentid,5);
     create temp table tmpchild as select * from child where
parent_id=fromid ;
     update tmpchild set parent_id=newparentid;
     insert into child select * from tmpchild;
     drop table tmpchild;
     return newparentid;
END; '
LANGUAGE 'plpgsql';

select parent_copy(1);
select parent_copy(2);
select * from parent;
select * from child;

--
Geoff,


6 Fifth Ave, St Morris, SA 5068  |   Fax: +618-8364-1543

2. ABF Apps

3. Array slice subscripts (was Re: [SQL] plpgsql function with more than one array argument)

4. Latest ado library?

5. _return double dimension array (table) from PLpgSQL procedure

6. PalmPilot synchronize to SQL Server?

7. Array slice subscripts (was Re: [SQL] plpgsql function

8. compacting access databse via visual basic code

9. About arrays in plpgsql.

10. How temporary are temporary tables?

11. pgsql/src/pl/plpgsql/src pl_exec.c plpgsql.h

12. pgsql/src/pl/plpgsql/src gram.y plpgsql.h

13. Binding an Oracle table/array as parameter to a Java array