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

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.
​