Re: [GENERAL] Cast SRF returning record to a table type?
On Sat, Apr 18, 2015 at 5:37 PM, Jim Nasby jim.na...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cast SRF returning record to a table type?
On Mon, Apr 20, 2015 at 7:57 AM, Merlin Moncure mmonc...@gmail.com wrote: On Sat, Apr 18, 2015 at 5:37 PM, Jim Nasby jim.na...@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: [GENERAL] Cast SRF returning record to a table type?
On Mon, Apr 20, 2015 at 9:40 AM, David G. Johnston david.g.johns...@gmail.com wrote: On Mon, Apr 20, 2015 at 7:57 AM, Merlin Moncure mmonc...@gmail.com wrote: On Sat, Apr 18, 2015 at 5:37 PM, Jim Nasby jim.na...@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: [GENERAL] Cast SRF returning record to a table type?
On Mon, Apr 20, 2015 at 11:40 AM, David G. Johnston david.g.johns...@gmail.com wrote: On Mon, Apr 20, 2015 at 7:57 AM, Merlin Moncure mmonc...@gmail.com wrote: On Sat, Apr 18, 2015 at 5:37 PM, Jim Nasby jim.na...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cast SRF returning record to a table type?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cast SRF returning record to a table type?
On 4/17/15 7:39 PM, David G. Johnston wrote: On Friday, April 17, 2015, Jim Nasby jim.na...@bluetreble.com mailto:jim.na...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cast SRF returning record to a table type?
On Friday, April 17, 2015, Jim Nasby jim.na...@bluetreble.com wrote: On 4/17/15 7:39 PM, David G. Johnston wrote: On Friday, April 17, 2015, Jim Nasby jim.na...@bluetreble.com mailto:jim.na...@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.
[GENERAL] Cast SRF returning record to a table type?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cast SRF returning record to a table type?
On Friday, April 17, 2015, Jim Nasby jim.na...@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.