Re: [GENERAL] Newbie question: returning rowtypes from a plpgsql function
Larry White <[EMAIL PROTECTED]> writes: > mysql> create table t as ( select * from table_x); That works in Postgres too. The SELECT INTO construct is a bit broken since, as you discovered, it has a different meaning in plpgsql than in the main SQL language. So I recommend using CREATE TABLE AS when you want to create a table this way. > The declarations section of the pl/pgsql documentation doesn't explain > how to declare a variable to represent a set of rows You can't. Possibly a cursor would help? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Newbie question: returning rowtypes from a plpgsql function
Please excuse my ignorance of databases and black holes. I don't have access to a Postgres db right now so I tried an experiment with mysql. Since they don't have a "select into" that creates a table, I tried this: mysql> create table t as ( select * from table_x); since table_x has no rows, I get: Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show tables; | Tables_in_test| | table_x| | t| +--+ 2 rows in set (0.02 sec) So it creates a table called t with no records and the same structure as table_x. That's what I thought the postgresql SELECT INTO would do. Now that I looked at the documentation more closely, I see that SELECT INTO returns a table when used in a query but an array of values when used in plpgsql, so that's at least part of what I have wrong. Having given it more thought, I think another error was to not declare the function as returning SETOF, so I can give that a try later. The declarations section of the pl/pgsql documentation doesn't explain how to declare a variable to represent a set of rows so if anyone can suggest something that would be helpful. Thanks. On Tue, 30 Nov 2004 22:58:11 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Larry White <[EMAIL PROTECTED]> writes: > > I wrote a function that returns a rowtype. The rowtype is assigned a > > value by a query using SELECT INTO. The query sometimes will return > > no rows. When it does, the function's return value is a row with no > > values. > > > I would have expected it to return 0 rows, like the query itself. > > How exactly would SELECT INTO return 0 rows? Perhaps the target > variables vanish into a black hole? > > regards, tom lane > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Newbie question: returning rowtypes from a plpgsql function
Larry White <[EMAIL PROTECTED]> writes: > I wrote a function that returns a rowtype. The rowtype is assigned a > value by a query using SELECT INTO. The query sometimes will return > no rows. When it does, the function's return value is a row with no > values. > I would have expected it to return 0 rows, like the query itself. How exactly would SELECT INTO return 0 rows? Perhaps the target variables vanish into a black hole? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Newbie question: returning rowtypes from a plpgsql function
I wrote a function that returns a rowtype. The rowtype is assigned a value by a query using SELECT INTO. The query sometimes will return no rows. When it does, the function's return value is a row with no values. I would have expected it to return 0 rows, like the query itself. Am I doing something wrong or is this the expected behavior? Is there a standard way to code around this? I expected my client code to check the number of rows returned to decide what to do next. thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
