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

"Cast" SRF returning record to a table type?

From

Jim Nasby

Date:

18 April 2015, 03:20:46

I'm working on a function that will return a set of test data, for unit
testing database stuff. It does a few things, but ultimately returns
SETOF record that's essentially:

RETURN QUERY EXECUTE 'SELECT * FROM ' || table_name;

Because it's always going to return a real relation, I'd like to be able
to the equivalent of:

SELECT ... FROM my_function( 'some_table' )::some_table;

Is there any trick that would allow that to work? I know that instead of
'SELECT * ...' I can do 'SELECT row(t.*) FROM ' || table_name || ' AS t'
and then do

SELECT ... FROM my_function( 'some_table' ) AS data( d some_table )

but I'm hoping to avoid the extra level of indirection.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

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

From

"David G. Johnston"

Date:

18 April 2015, 03:39:34

On Friday, April 17, 2015, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

I'm working on a function that will return a set of test data, for unit testing database stuff. It does a few things, but ultimately returns SETOF record that's essentially:

RETURN QUERY EXECUTE 'SELECT * FROM ' || table_name;

Because it's always going to return a real relation, I'd like to be able to the equivalent of:

SELECT ... FROM my_function( 'some_table' )::some_table;

Unfortunately this means "cast the existing type to some_table" and "record" is not a valid type in this context.

Is there any trick that would allow that to work? I know that instead of 'SELECT * ...' I can do 'SELECT row(t.*) FROM ' || table_name || ' AS t' and then do

SELECT ... FROM my_function( 'some_table' ) AS data( d some_table )

but I'm hoping to avoid the extra level of indirection.

Haven't explored this specific code in depth...but which part - the function alias or the select row(t.*)? They seem to be independent concerns.

David J.

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

From

Jim Nasby

Date:

18 April 2015, 07:01:20

On 4/17/15 7:39 PM, David G. Johnston wrote:
> On Friday, April 17, 2015, Jim Nasby <Jim.Nasby@bluetreble.com
> <mailto:Jim.Nasby@bluetreble.com>> wrote:
>
>     I'm working on a function that will return a set of test data, for
>     unit testing database stuff. It does a few things, but ultimately
>     returns SETOF record that's essentially:
>
>     RETURN QUERY EXECUTE 'SELECT * FROM ' || table_name;
>
>     Because it's always going to return a real relation, I'd like to be
>     able to the equivalent of:
>
>     SELECT ... FROM my_function( 'some_table' )::some_table;
>
>
> Unfortunately this means "cast the existing type to some_table" and
> "record" is not a valid type in this context.
>
>
>     Is there any trick that would allow that to work? I know that
>     instead of 'SELECT * ...' I can do 'SELECT row(t.*) FROM ' ||
>     table_name || ' AS t' and then do
>
>     SELECT ... FROM my_function( 'some_table' ) AS data( d some_table )
>
>     but I'm hoping to avoid the extra level of indirection.
>
> Haven't explored this specific code in depth...but which part - the
> function alias or the select row(t.*)?  They seem to be independent
> concerns.

I'm saying that I know I can use the row construct as a poor
work-around. What I actually want though is a way to tell this query:

SELECT ... FROM my_function( 'some_table' )

that my_function is returning a record that exactly matches "my_table".
I suspect there's not actually any way to do that :(
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

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

From

"David G. Johnston"

Date:

18 April 2015, 08:47:21

On Friday, April 17, 2015, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

On 4/17/15 7:39 PM, David G. Johnston wrote:
On Friday, April 17, 2015, Jim Nasby <Jim.Nasby@bluetreble.com
<mailto:Jim.Nasby@bluetreble.com>> wrote:

I'm working on a function that will return a set of test data, for
unit testing database stuff. It does a few things, but ultimately
returns SETOF record that's essentially:

RETURN QUERY EXECUTE 'SELECT * FROM ' || table_name;

Because it's always going to return a real relation, I'd like to be
able to the equivalent of:

SELECT ... FROM my_function( 'some_table' )::some_table;

Unfortunately this means "cast the existing type to some_table" and
"record" is not a valid type in this context.

Is there any trick that would allow that to work? I know that
instead of 'SELECT * ...' I can do 'SELECT row(t.*) FROM ' ||
table_name || ' AS t' and then do

SELECT ... FROM my_function( 'some_table' ) AS data( d some_table )

but I'm hoping to avoid the extra level of indirection.

Haven't explored this specific code in depth...but which part - the
function alias or the select row(t.*)? They seem to be independent
concerns.

I'm saying that I know I can use the row construct as a poor work-around. What I actually want though is a way to tell this query:

SELECT ... FROM my_function( 'some_table' )

that my_function is returning a record that exactly matches "my_table". I suspect there's not actually any way to do that :(

No matter what you do inside the function you have to write that last query as "from my_function('some_table') AS (rel some_table)" otherwise the planer is clueless. You cannot defer the type until runtime. Your cast form is slightly more succinct but I cannot see making it work when the current method is serviceable.

Inside the function I would have thought that select * shoud work - no need to use the row(t.*) construct - but the later seems reasonably direct...

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

Select ... From my_func(null::some_table)

Create function my_func(tbl any) returns setof any ....

Use typeof to get a text string of the tbl arg's type.

You could maybe also return a refcursor...

David J.

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

From

Jim Nasby

Date:

19 April 2015, 01:37:54

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).*

that gives you the same output as if you'd selected directly from the table.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

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

From

Merlin Moncure

Date:

20 April 2015, 17:57:26

On Sat, Apr 18, 2015 at 5:37 PM, Jim Nasby <Jim.Nasby@bluetreble.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)

merlin

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

From

"David G. Johnston"

Date:

20 April 2015, 19:40:12

On Mon, Apr 20, 2015 at 7:57 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Sat, Apr 18, 2015 at 5:37 PM, Jim Nasby <Jim.Nasby@bluetreble.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.

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

From

"David G. Johnston"

Date:

20 April 2015, 19:42:44

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

On Mon, Apr 20, 2015 at 7:57 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
On Sat, Apr 18, 2015 at 5:37 PM, Jim Nasby <Jim.Nasby@bluetreble.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.

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

From

Merlin Moncure

Date:

20 April 2015, 22:43:27

On Mon, Apr 20, 2015 at 11:40 AM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Mon, Apr 20, 2015 at 7:57 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>
>> On Sat, Apr 18, 2015 at 5:37 PM, Jim Nasby <Jim.Nasby@bluetreble.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)

none at all: this is better since pg_typeof() automatically
quote_idents (something which I did not know but verified!).

merlin