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
><pavel.steh...@gmail.com>:
>
>
>
>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?
>>>>>>
>>>>>
>>>>
>>>
>>
>
