Re: Execute ignoring cursor?

2016-04-11 13:11 GMT+02:00 nummervet nummervet <nummervet(at)mail(dot)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(dot)stehule(at)gmail(dot)com>:
>
>
>
>
> 2016-04-08 16:46 GMT+02:00 nummervet nummervet <nummervet(at)mail(dot)ru
> <https://e.mail.ru/compose/?mailto=mailto%3anummervet(at)mail(dot)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(dot)stehule(at)gmail(dot)com
> <https://e.mail.ru/compose/?mailto=mailto%3apavel(dot)stehule(at)gmail(dot)com>>:
>
>
> Hi
>
> 2016-04-08 16:17 GMT+02:00 nummervet nummervet <nummervet(at)mail(dot)ru
> <https://e.mail.ru/compose/?mailto=mailto%3anummervet(at)mail(dot)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?
>
>
>
>
>
>