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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Larry White <ljw1001(at)gmail(dot)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
>