[HACKERS] Re[2]: [HACKERS] Execute ignoring cursor?
Oh. That doesn't work for me as i generate the query dynamically and don't know their structure... Maybe there is an easy way to get the cursor structure (column - value, column - value)? Or should i give up on cursors and try something else? Some Google search hint that hstore could be my saviour :) >Понедельник, 11 апреля 2016, 16:10 +03:00 от Pavel Stehule >: > > > >2016-04-11 13:11 GMT+02:00 nummervet nummervet < nummer...@mail.ru > : >>Ok, now i am getting this: >>ERROR: could not identify column "151" in record data type >> >>Raise notice show that the column exists. >>Any other way around it? >> > >hmm - it doesn't work for generic record - it should be typed row value. > >postgres=# create table foo("123" int); >CREATE TABLE > >postgres=# create table boo("123" int); >CREATE TABLE > >insert into boo values(20); >INSERT 0 1 > >postgres=# do $$ >declare r boo; -- cannot be generic record >begin > for r in select * from boo > loop > execute $_$insert into foo values($1."123")$_$ using r; > end loop; >end; >$$; >DO > >Regards > >Pavel > > >> >>>Пятница, 8 апреля 2016, 18:24 +03:00 от Pavel Stehule < >>>pavel.steh...@gmail.com >: >>> >>> >>> >>> >>>2016-04-08 16:46 GMT+02:00 nummervet nummervet < nummer...@mail.ru > : >>>>That didn't work for me: >>>> >>>>ERROR: syntax error at or near "$" >>>>LINE 1: ...ibute_id, set_id ) (select $."151", '... >>> >>>should be $1 >>> >>>Regards >>> >>>Pavel >>> >>>> >>>> >>>>>Пятница, 8 апреля 2016, 17:25 +03:00 от Pavel Stehule < >>>>>pavel.steh...@gmail.com >: >>>>> >>>>> >>>>>Hi >>>>> >>>>>2016-04-08 16:17 GMT+02:00 nummervet nummervet < nummer...@mail.ru > : >>>>>>Hello. Didn't find dedicated plpgsql list, so decided to post question >>>>>>here. >>>>>>I am trying to create a function that will pick up some values from >>>>>>cursor and execute them as a dynamic query. >>>>>>However, once i use EXECUTE, its seems to be ignoring the existence of >>>>>>cursor and try to pick up values from table. >>>>>>Basically: >>>>>> >>>>>>insert into mytable ( value, attribute_id, set_id ) (select rec."151", >>>>>>'201', '1') >>>>>> >>>>>>works, but >>>>>> >>>>>>execute 'insert into mytable ( value, attribute_id, set_id ) (select >>>>>>rec."151", ''201'', ''1'')' >>>>> >>>>>Dynamic queries are executed in own space and there are not direct access >>>>>to plpgsql variables. >>>>> >>>>>please, try: execute 'insert into mytable ( value, attribute_id, set_id ) >>>>>(select $1."151", ''201'', ''1'')' using rec; >>>>> >>>>>The content should be passed to dynamic query via USING clause. >>>>> >>>>>http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN >>>>> >>>>>Regards >>>>> >>>>>Pavel Stehule >>>>> >>>>>> >>>>>>fails with >>>>>> >>>>>>ERROR: missing FROM-clause entry for table "rec" >>>>>>LINE 1: ...ibute_id, set_id ) (select rec."151",... >>>>>> >>>>>>Is there any way around it? Or should i just give up and do it some other >>>>>>way? >>>>>> >>>>> >>>> >>> >> >
[HACKERS] Re[4]: [HACKERS] Execute ignoring cursor?
Ok, now i am getting this: ERROR: could not identify column "151" in record data type Raise notice show that the column exists. Any other way around it? >Пятница, 8 апреля 2016, 18:24 +03:00 от Pavel Stehule >: > > > >2016-04-08 16:46 GMT+02:00 nummervet nummervet < nummer...@mail.ru > : >>That didn't work for me: >> >>ERROR: syntax error at or near "$" >>LINE 1: ...ibute_id, set_id ) (select $."151", '... > >should be $1 > >Regards > >Pavel > >> >> >>>Пятница, 8 апреля 2016, 17:25 +03:00 от Pavel Stehule < >>>pavel.steh...@gmail.com >: >>> >>> >>>Hi >>> >>>2016-04-08 16:17 GMT+02:00 nummervet nummervet < nummer...@mail.ru > : >>>>Hello. Didn't find dedicated plpgsql list, so decided to post question here. >>>>I am trying to create a function that will pick up some values from cursor >>>>and execute them as a dynamic query. >>>>However, once i use EXECUTE, its seems to be ignoring the existence of >>>>cursor and try to pick up values from table. >>>>Basically: >>>> >>>>insert into mytable ( value, attribute_id, set_id ) (select rec."151", >>>>'201', '1') >>>> >>>>works, but >>>> >>>>execute 'insert into mytable ( value, attribute_id, set_id ) (select >>>>rec."151", ''201'', ''1'')' >>> >>>Dynamic queries are executed in own space and there are not direct access to >>>plpgsql variables. >>> >>>please, try: execute 'insert into mytable ( value, attribute_id, set_id ) >>>(select $1."151", ''201'', ''1'')' using rec; >>> >>>The content should be passed to dynamic query via USING clause. >>> >>>http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN >>> >>>Regards >>> >>>Pavel Stehule >>> >>>> >>>>fails with >>>> >>>>ERROR: missing FROM-clause entry for table "rec" >>>>LINE 1: ...ibute_id, set_id ) (select rec."151",... >>>> >>>>Is there any way around it? Or should i just give up and do it some other >>>>way? >>>> >>> >> >
[HACKERS] Re[2]: [HACKERS] Execute ignoring cursor?
That didn't work for me: ERROR: syntax error at or near "$" LINE 1: ...ibute_id, set_id ) (select $."151", '... >Пятница, 8 апреля 2016, 17:25 +03:00 от Pavel Stehule >: > >Hi > >2016-04-08 16:17 GMT+02:00 nummervet nummervet < nummer...@mail.ru > : >>Hello. Didn't find dedicated plpgsql list, so decided to post question here. >>I am trying to create a function that will pick up some values from cursor >>and execute them as a dynamic query. >>However, once i use EXECUTE, its seems to be ignoring the existence of cursor >>and try to pick up values from table. >>Basically: >> >>insert into mytable ( value, attribute_id, set_id ) (select rec."151", '201', >>'1') >> >>works, but >> >>execute 'insert into mytable ( value, attribute_id, set_id ) (select >>rec."151", ''201'', ''1'')' > >Dynamic queries are executed in own space and there are not direct access to >plpgsql variables. > >please, try: execute 'insert into mytable ( value, attribute_id, set_id ) >(select $1."151", ''201'', ''1'')' using rec; > >The content should be passed to dynamic query via USING clause. > >http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > >Regards > >Pavel Stehule > >> >>fails with >> >>ERROR: missing FROM-clause entry for table "rec" >>LINE 1: ...ibute_id, set_id ) (select rec."151",... >> >>Is there any way around it? Or should i just give up and do it some other way? >> >
[HACKERS] Execute ignoring cursor?
Hello. Didn't find dedicated plpgsql list, so decided to post question here. I am trying to create a function that will pick up some values from cursor and execute them as a dynamic query. However, once i use EXECUTE, its seems to be ignoring the existence of cursor and try to pick up values from table. Basically: insert into mytable ( value, attribute_id, set_id ) (select rec."151", '201', '1') works, but execute 'insert into mytable ( value, attribute_id, set_id ) (select rec."151", ''201'', ''1'')' fails with ERROR: missing FROM-clause entry for table "rec" LINE 1: ...ibute_id, set_id ) (select rec."151",... Is there any way around it? Or should i just give up and do it some other way?