Обсуждение: select as params to function
Hi all, Trying to clean up some data so I wrote myself a function which will insert the new stuff okay but the existing data... I'm not sure how to run it through the function. To make life a little simpler on myself, the old data is in table webserv2 and the new data will go into table webserv2a. The function insert_webserv2 takes the old arguments, cleans things up, and inserts into webserv2a. All is well there. But I have a table full of stuff in webserv2 that I'd like moved over. I'd love to do: select insert_webserv2( (select * from webserv2) ); but that obviously wont work. Is there a simple way to feed a table through the function? -Andy
Hello PostgreSQL has no table data type. You can use a array of records instead and then select fx((select array(select * from tab))); regards Pavel Stehule 2012/2/17 Andy Colson <andy@squeakycode.net>: > Hi all, > > Trying to clean up some data so I wrote myself a function which will insert > the new stuff okay but the existing data... I'm not sure how to run it > through the function. > > To make life a little simpler on myself, the old data is in table webserv2 > and the new data will go into table webserv2a. > > The function insert_webserv2 takes the old arguments, cleans things up, and > inserts into webserv2a. > > All is well there. But I have a table full of stuff in webserv2 that I'd > like moved over. > > I'd love to do: > > select insert_webserv2( (select * from webserv2) ); > > but that obviously wont work. Is there a simple way to feed a table through > the function? > > -Andy > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On 2/17/2012 2:11 PM, Pavel Stehule wrote: > Hello > > PostgreSQL has no table data type. You can use a array of records instead > > and then > > select fx((select array(select * from tab))); > > regards > > Pavel Stehule > > 2012/2/17 Andy Colson<andy@squeakycode.net>: >> Hi all, >> >> Trying to clean up some data so I wrote myself a function which will insert >> the new stuff okay but the existing data... I'm not sure how to run it >> through the function. >> >> To make life a little simpler on myself, the old data is in table webserv2 >> and the new data will go into table webserv2a. >> >> The function insert_webserv2 takes the old arguments, cleans things up, and >> inserts into webserv2a. >> >> All is well there. But I have a table full of stuff in webserv2 that I'd >> like moved over. >> >> I'd love to do: >> >> select insert_webserv2( (select * from webserv2) ); >> >> but that obviously wont work. Is there a simple way to feed a table through >> the function? >> >> -Andy >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > Humm, I can't seem to get that to work: stats=# select insert_webserv2((select array (select * from webserv2 limit 1)) ); ERROR: subquery must return only one column LINE 1: select insert_webserv2((select array (select * from webserv2... tried array_agg too: stats=# select insert_webserv2((select array_agg (select * from webserv2 limit 1)) ); ERROR: syntax error at or near "select" LINE 1: select insert_webserv2((select array_agg (select * from webs... -Andy
On Fri, Feb 17, 2012 at 2:32 PM, Andy Colson <andy@squeakycode.net> wrote: > On 2/17/2012 2:11 PM, Pavel Stehule wrote: >> >> Hello >> >> PostgreSQL has no table data type. You can use a array of records instead >> >> and then >> >> select fx((select array(select * from tab))); select fx(array(select t from tab t)); fx takes tab[] merlin
On 2/17/2012 2:49 PM, Merlin Moncure wrote: > On Fri, Feb 17, 2012 at 2:32 PM, Andy Colson<andy@squeakycode.net> wrote: >> On 2/17/2012 2:11 PM, Pavel Stehule wrote: >>> >>> Hello >>> >>> PostgreSQL has no table data type. You can use a array of records instead >>> >>> and then >>> >>> select fx((select array(select * from tab))); > > select fx(array(select t from tab t)); > > fx takes tab[] > > merlin > Oh, I see, I need to change my function too. Thanks, -Andy