2016-04-11 16:31 GMT+02:00 nummervet nummervet <nummer...@mail.ru>: > 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 :) >
maybe hstore, or json, or C extension - I wrote plpgsql toolbox https://github.com/okbob/pltoolbox . Another way is using PLPerl, PLPythonu. PLpgSQL is strongly strict language - it is not designed for dynamic tasks. Regards Pavel > > Понедельник, 11 апреля 2016, 16:10 +03:00 от Pavel Stehule < > pavel.steh...@gmail.com>: > > > > > 2016-04-11 13:11 GMT+02:00 nummervet nummervet <nummer...@mail.ru > <https://e.mail.ru/compose/?mailto=mailto%3anummer...@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 > <https://e.mail.ru/compose/?mailto=mailto%3apavel.steh...@gmail.com>>: > > > > > 2016-04-08 16:46 GMT+02:00 nummervet nummervet <nummer...@mail.ru > <https://e.mail.ru/compose/?mailto=mailto%3anummer...@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 > <https://e.mail.ru/compose/?mailto=mailto%3apavel.steh...@gmail.com>>: > > > Hi > > 2016-04-08 16:17 GMT+02:00 nummervet nummervet <nummer...@mail.ru > <https://e.mail.ru/compose/?mailto=mailto%3anummer...@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? > > > > > > > >