Re: 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 <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
>