Re: "Cast" SRF returning record to a table type?

On Mon, Apr 20, 2015 at 9:40 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Mon, Apr 20, 2015 at 7:57 AM, Merlin Moncure <mmoncure(at)gmail(dot)com>
> wrote:
>
>> On Sat, Apr 18, 2015 at 5:37 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>
>> wrote:
>> > On 4/18/15 12:47 AM, David G. Johnston wrote:
>> >>
>> >> If you could find a way to pass a value of type some_table into the
>> >> function - instead of the name/text 'some_table‘ - you could possibly
>> >> use polymorphic pseudotypes...just imagining here...
>> >
>> >
>> > Oh, I didn't think about that. Maybe I'll try it.
>> >
>> > What I ended up with is this:
>> >
>> > CREATE FUNCTION ... (
>> > ) RETURNS SETOF text ...
>> > ...
>> > RETURN QUERY EXECUTE format(
>> > 'SELECT row(t.*)::text FROM %I.%I AS t'
>> > , ...
>> > );
>> >
>> > So the function is getting a record and casting it to text. To call the
>> > function you have to...
>> >
>> > SELECT (function(...))::name_of_table).*
>>
>> *do not do this*. If table has three fields a,b,c, the query will expand
>> to:
>>
>> SELECT function(...).a, function(...).b, function(...).c;
>>
>> SRF in column list (now that we have LATERAL) can now be considered a
>> 'bad practice' in most cases I can think of (possibly exempting
>> trivial data productions with generate_series, etc).
>>
>> > that gives you the same output as if you'd selected directly from the
>> table.
>>
>> I think the following is better:
>>
>> postgres=# create table foo(id int, b text);
>> CREATE TABLE
>>
>> postgres=# insert into foo select s, s || '_test' from
>> generate_series(1,3) s;
>> INSERT 0 3
>>
>> create or replace function getdata(r anyelement, tablename text)
>> returns setof anyelement as
>> $$
>> begin
>> return query execute format('select * from %s', quote_ident(tablename));
>> end;
>> $$ language plpgsql;
>> CREATE FUNCTION
>>
>> postgres=# select * from getdata(null::foo, 'foo');
>> id │ b
>> ────┼────────
>> 1 │ 1_test
>> 2 │ 2_test
>> 3 │ 3_test
>> (3 rows)
>>
>>
> ​Any particular reason you wouldn't write the function this way?
>
> create or replace function getdata(r anyelement)
> returns setof anyelement as
> $$
> begin
> return query execute format('select * from %I', pg_typeof(r));
> end;
> $$ language plpgsql;
>
> Specifically, using pg_typeof(r) instead of passing in the table name
> twice; and using "%I" instead of "%s" + quote_ident(...)
>
> Replacing the above function still provides the same results.
>
> Agreed this really wants to called in the FROM clause.
>
> David J.
>
>
​FWIW - I was inspired by Java's "Generics" handling for coming up with
this possibility.

David J.