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
Larry White <ljw1001@gmail.com> 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
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.
Show quoted text
On Tue, 30 Nov 2004 22:58:11 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Larry White <ljw1001@gmail.com> 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
Larry White <ljw1001@gmail.com> 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