Обсуждение: Record with a field consisting of table rows
Hi,
I am trying to create a PL/pgSQL function whose return type is a tuple
consisting of an integer and a list of table rows. I emulate the tuple
by defining a record 'page_t' with the two fields; however, the naïve
approach of doing a SELECT INTO one the record's fields does not work
(see function 'get_page') below. Am I missing something obvious here?
Thanks in advance!
Jon
create table users
(
uid int4 not null,
name text not null,
age int4 not null,
primary key (uid)
);
create type user_t AS
(
uid int4,
name text,
age int4
);
create type page_t AS
(
total int4,
users user_t[]
);
create function get_page ()
returns page_t
language plpgsql as
$$
declare
_page page_t;
begin
_page.total := select count (*) from users;
select * into _page.users from users limit 10;
return _page;
end
$$;
Hello try to SELECT INTO ARRAY(SELECT user_t FROM users LIMIT 10) _page.users; Regards Pavel Stehule 2011/1/13 Jon Smark <jon.smark@yahoo.com>: > Hi, > > I am trying to create a PL/pgSQL function whose return type is a tuple > consisting of an integer and a list of table rows. I emulate the tuple > by defining a record 'page_t' with the two fields; however, the naïve > approach of doing a SELECT INTO one the record's fields does not work > (see function 'get_page') below. Am I missing something obvious here? > > Thanks in advance! > Jon > > > create table users > ( > uid int4 not null, > name text not null, > age int4 not null, > primary key (uid) > ); > > create type user_t AS > ( > uid int4, > name text, > age int4 > ); > > create type page_t AS > ( > total int4, > users user_t[] > ); > > create function get_page () > returns page_t > language plpgsql as > $$ > declare > _page page_t; > begin > _page.total := select count (*) from users; > select * into _page.users from users limit 10; > return _page; > end > $$; > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Jon Smark <jon.smark@yahoo.com> writes:
> create type page_t AS
> (
> total int4,
> users user_t[]
> );
> create function get_page ()
> returns page_t
> language plpgsql as
> $$
> declare
> _page page_t;
> begin
> _page.total := select count (*) from users;
> select * into _page.users from users limit 10;
> return _page;
> end
> $$;
That is certainly not going to work: that select does not produce an
array, it produces a column of user_t (of which SELECT INTO is only
gonna take the first, anyway).
Untested, but I think you'd have better results with
_page.users := array(select users from users limit 10);
It'd also be smart to get rid of user_t and rely directly on the "users"
rowtype associated with the users table.
regards, tom lane
Hi, > That is certainly not going to work: that select does not produce an > array, it produces a column of user_t (of which SELECT INTO is only > gonna take the first, anyway). > > Untested, but I think you'd have better results with > > _page.users := array(select users from users limit 10); The above does work, thanks. There is however one drawback: the type associated with _page.users is now an array. Is there a way to make it a 'SETOF user_t'? Best regards, Jon
2011/1/13 Jon Smark <jon.smark@yahoo.com>: > Hi, > >> That is certainly not going to work: that select does not produce an >> array, it produces a column of user_t (of which SELECT INTO is only >> gonna take the first, anyway). >> >> Untested, but I think you'd have better results with >> >> _page.users := array(select users from users limit 10); > > The above does work, thanks. There is however one drawback: the type > associated with _page.users is now an array. Is there a way to make > it a 'SETOF user_t'? > no. PostgreSQL doesn't supports SET. Only arrays are supported. Regards Pavel > Best regards, > Jon > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On 13 Jan 2011, at 19:25, Pavel Stehule wrote: >> The above does work, thanks. There is however one drawback: the type >> associated with _page.users is now an array. Is there a way to make >> it a 'SETOF user_t'? >> > > no. PostgreSQL doesn't supports SET. Only arrays are supported. I'm not sure what you mean here, Postgres certainly _does_ support set-returning functions. Maybe you were referring to somethingin the particular context of the problem the OP is trying to solve? It would be kind of bad if people Google for this topic and would come back with the wrong conclusion. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4d2f4de411871554341128!
On 13 Jan 2011, at 17:22, Jon Smark wrote:
> create type page_t AS
> (
> total int4,
> users user_t[]
> );
>
> create function get_page ()
> returns page_t
> language plpgsql as
> $$
> declare
> _page page_t;
> begin
> _page.total := select count (*) from users;
> select * into _page.users from users limit 10;
> return _page;
> end
> $$;
I think it would be easier to rewrite that to a set-returning function returning TABLE (...).
Something like this (untested):
create function get_page ()
returns setof table (total int, user users)
language plpgsql as
$$
declare
_total int;
begin
_total := select count (*) from users;
return query select _total AS total, u from users AS u limit 10;
end
$$;
In general it is considered a bad idea to rely on what * returns though, it's better to return the columns explicitly.
Which makes me wonder, is table cloning supported for these cases? For example:
create function get_page ()
returns setof table (LIKE users, total int)
...
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4d2f50bf11877227918328!
2011/1/13 Alban Hertroys <dalroi@solfertje.student.utwente.nl>: > On 13 Jan 2011, at 19:25, Pavel Stehule wrote: > >>> The above does work, thanks. There is however one drawback: the type >>> associated with _page.users is now an array. Is there a way to make >>> it a 'SETOF user_t'? >>> >> >> no. PostgreSQL doesn't supports SET. Only arrays are supported. > > > I'm not sure what you mean here, Postgres certainly _does_ support set-returning functions. Maybe you were referring tosomething in the particular context of the problem the OP is trying to solve? > The name of feature "SET RETURNED FUNC" doesn't mean so PostgreSQL supports SET type in ANSI SQL sense. Regards Pavel Stehule > It would be kind of bad if people Google for this topic and would come back with the wrong conclusion. > > Alban Hertroys > > -- > Screwing up is an excellent way to attach something to the ceiling. > > > !DSPAM:1030,4d2f4de011871371264419! > > >
On 13 Jan 2011, at 20:21, Pavel Stehule wrote: >> I'm not sure what you mean here, Postgres certainly _does_ support set-returning functions. Maybe you were referring tosomething in the particular context of the problem the OP is trying to solve? >> > > The name of feature "SET RETURNED FUNC" doesn't mean so PostgreSQL > supports SET type in ANSI SQL sense. I think this is getting off topic, but I don't understand what you're trying to say here. That's probably partially due toyour odd usage of the word "so" - I think you mean to use it as "that" (which it doesn't mean), and not as "because" (whichits meaning is much closer to). You might also want to elaborate a bit more so that people have enough context to work around your grammar ;) Anyway, are you saying: 1. PostgreSQL doesn't support SRF's (It does though)? Or that, 2. ANSI SQL doesn't support SRF's? Or that 3. PostgreSQL's implementation of SRF's is not compatible with the ANSI SQL definition? Or something entirely different? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d2f54ac11871133719602!
2011/1/13 Alban Hertroys <dalroi@solfertje.student.utwente.nl>: > On 13 Jan 2011, at 20:21, Pavel Stehule wrote: > >>> I'm not sure what you mean here, Postgres certainly _does_ support set-returning functions. Maybe you were referringto something in the particular context of the problem the OP is trying to solve? >>> >> >> The name of feature "SET RETURNED FUNC" doesn't mean so PostgreSQL >> supports SET type in ANSI SQL sense. > > > I think this is getting off topic, but I don't understand what you're trying to say here. That's probably partially dueto your odd usage of the word "so" - I think you mean to use it as "that" (which it doesn't mean), and not as "because"(which its meaning is much closer to). > You might also want to elaborate a bit more so that people have enough context to work around your grammar ;) > > Anyway, are you saying: > 1. PostgreSQL doesn't support SRF's (It does though)? Or that, > 2. ANSI SQL doesn't support SRF's? Or that > 3. PostgreSQL's implementation of SRF's is not compatible with the ANSI SQL definition? > sorry, my English isn't good. A problem is in meaning of keyword "SET". ANSI SQL knows a datatype "SET". This datatype isn't supported by pg. A SRF functions are called table functions in ANSI SQL. But I can be messed Pavel > Or something entirely different? > > Alban Hertroys > > -- > If you can't see the forest for the trees, > cut the trees and you'll see there is no forest. > > > !DSPAM:1030,4d2f54a811877157859450! > > >
Hi, This thread has veered off-topic, but my original problem still remains. Basically, I want to declare a function that returns a tuple consisting of an integer and a set of table rows. Something like the following: CREATE FUNCTION foobar () RETURNS (int4, SETOF users) Now, if I understand correctly, the only way to have a function return a tuple is to create a named record and have the function return that record: CREATE TYPE page_t AS ( total int4 users SETOF users ) Which is of course not grammatical. So, how does one solve this problem in PL/pgSQL? Thanks in advance! Jon
2011/1/14 Jon Smark <jon.smark@yahoo.com>: > Hi, > > This thread has veered off-topic, but my original problem still remains. > Basically, I want to declare a function that returns a tuple consisting > of an integer and a set of table rows. Something like the following: > > CREATE FUNCTION foobar () RETURNS (int4, SETOF users) > you have to use a array CREATE FUNCTION foo(... OUT total int, OUT _users users[]) there are no other way Regards Pavel Stehule > Now, if I understand correctly, the only way to have a function return > a tuple is to create a named record and have the function return that > record: > > CREATE TYPE page_t AS > ( > total int4 > users SETOF users > ) > > Which is of course not grammatical. So, how does one solve this problem > in PL/pgSQL? > > Thanks in advance! > Jon > > > > >
On 14 Jan 2011, at 16:03, Jon Smark wrote: > Hi, > > This thread has veered off-topic, but my original problem still remains. > Basically, I want to declare a function that returns a tuple consisting > of an integer and a set of table rows. Something like the following: > > CREATE FUNCTION foobar () RETURNS (int4, SETOF users) > > Now, if I understand correctly, the only way to have a function return > a tuple is to create a named record and have the function return that > record: Nope, see my reply from yesterday around 20:23 You can return a table instead, with the count added as an extra column. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4d308e2711871421280230!
Hi,
> Nope, see my reply from yesterday around 20:23
> You can return a table instead, with the count added as an
> extra column.
I did see your solution, but note that it does not return a tuple
consisting of an integer and a setof (as I wanted), but instead
returns a setof of a tuple.
I still haven't found a solution to the original problem. The
best I can do so far is to create a function that returns a tuple
consisting of an int and the first row of table results (see below).
Any more thoughts?
Best regards,
Jon
create table users
(
uid int4 not null,
name text not null,
age int4 not null,
primary key (uid)
);
create type page_t AS
(
total int4,
users users
);
create function get_page ()
returns page_t
language plpgsql as
$$
declare
_total int4;
_users users;
_page page_t;
begin
select count (*) from users into _total;
select * from users limit 10 into _users;
_page := row (_total, _users);
return _page;
end
$$;
On 15 Jan 2011, at 17:01, Jon Smark wrote: > Hi, > >> Nope, see my reply from yesterday around 20:23 >> You can return a table instead, with the count added as an >> extra column. > > I did see your solution, but note that it does not return a tuple > consisting of an integer and a setof (as I wanted), but instead > returns a setof of a tuple. No, of course not. A function cannot return different amounts of different return-values in any language I know of. You don't seem to grasp the essence of what a set-returning function does; they are similar to Iterators in Java or functionsthat yield a result (as opposed to returning one) in Python or cursors in SQL. You can't mix that with returning a single value, unless you do that each time the function is called for the next iteration(which happens to be what I did in my earlier example). You could do something ugly by collecting the results in memory until you eventually return them in an array, but that wouldhardly be practical if your data sets get a little large. You probably can return a tuple consisting of an int and a refcursor though. You'll need another function to loop throughthe refcursor to fetch the results, but you would sort of get what you apparently want. I can't see why you'd wantthat though. You may also be able to return the count as an OUT-parameter, but I'm not sure you can mix that with returning a SETOF somethingelse. I expect you'll find out that isn't possible. Obtaining both results from the function (if it _is_ possible)will be tricky I think. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d31da3e11871342110173!
Hi, > No, of course not. A function cannot return different > amounts of different return-values in any language I know > of. Come on, you make it sound like it's something inconceivable or exotic, even though pretty much any strongly-typed language with a post-1970s type-system will allow a function to return tuples of arbitrary values. Have you never come across Ocaml, Haskell, Scala, and so forth? > You don't seem to grasp the essence of what a set-returning > function does; they are similar to Iterators in Java or > functions that yield a result (as opposed to returning one) > in Python or cursors in SQL. > > You can't mix that with returning a single value, unless > you do that each time the function is called for the next > iteration (which happens to be what I did in my earlier > example). > > You could do something ugly by collecting the results in > memory until you eventually return them in an array, but > that would hardly be practical if your data sets get a > little large. > > You probably can return a tuple consisting of an int and a > refcursor though. You'll need another function to loop > through the refcursor to fetch the results, but you would > sort of get what you apparently want. I can't see why you'd > want that though. Yes, the alternatives are indeed more cumbersome than they're worth. I might as well split the original function into two separate functions... Cheers, Jon
On 15 Jan 2011, at 21:57, Jon Smark wrote:
>> No, of course not. A function cannot return different
>> amounts of different return-values in any language I know
>> of.
>
> Come on, you make it sound like it's something inconceivable or exotic,
> even though pretty much any strongly-typed language with a post-1970s
> type-system will allow a function to return tuples of arbitrary values.
> Have you never come across Ocaml, Haskell, Scala, and so forth?
A tuple wouldn't be unusual, but you're not asking for a simple tuple here. What you're asking for is a value and a set
oftuples. That would sort of look like this:
total | users
---------------------------------------------------------
3 | foo
| bar
| baz
And what's worse, after the first returned tuple you only seem to want the users value, without the total value. I
don'tknow of any language that can do that and I'm pretty sure the languages you mentioned can't do that either. It
makesparsing the return values unnecessary difficult, I bet that's a compelling enough reason that nobody seems to have
implementedit.
What most languages, including pgsql, _can_ do is this:
total | users
---------------------------------------------------------
3 | foo
3 | bar
3 | baz
Or this:
total | users
---------------------------------------------------------
3 | [foo, bar, baz]
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4d32398a11872893390349!
Hi,
> A tuple wouldn't be unusual, but you're not asking for a
> simple tuple here. What you're asking for is a value and a
> set of tuples.
Which is in itself a tuple! This discussion is again veering slightly
off-topic, but note that tuples as mathematical objects do not have such
arbitrary limitations.
> And what's worse, after the first returned tuple you only
> seem to want the users value, without the total value. I
> don't know of any language that can do that and I'm pretty
> sure the languages you mentioned can't do that either. It
> makes parsing the return values unnecessary difficult, I bet
> that's a compelling enough reason that nobody seems to have
> implemented it.
Again, you're making it sound like I'm requesting something exotic,
while any language with a modern type system allows this and much
more without blinking. In Ocaml, for example, the return type 'page_t'
of my function could be defined as follows:
type user_t = { uid: int; name: string; age: int; }
type page_t = int * user_t list
('user_'t is declared as a record with fields 'uid', 'name', and 'age';
'page_t' is defined as a tuple consisting of an integer and a list of
'user_t').
Moreover, we've already established that PL/pgSQL also allows the return
of a tuple consisting of an integer and an array of tuples. All I wanted
was to change the latter portion into SETOF tuples...
Best regards,
Jon
On 16 Jan 2011, at 17:40, Jon Smark wrote:
> Again, you're making it sound like I'm requesting something exotic,
> while any language with a modern type system allows this and much
> more without blinking. In Ocaml, for example, the return type 'page_t'
> of my function could be defined as follows:
>
> type user_t = { uid: int; name: string; age: int; }
> type page_t = int * user_t list
That's the refcursor option I gave you earlier.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4d33222411702065417953!