Re: Execute ignoring cursor?
| От | Pavel Stehule |
|---|---|
| Тема | Re: Execute ignoring cursor? |
| Дата | |
| Msg-id | CAFj8pRAeC-Wr4+E6pBs7n3TUmLah6DUzdY0agrCD6M3tQ6i6gw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re[4]: [HACKERS] Execute ignoring cursor? (nummervet nummervet <nummervet@mail.ru>) |
| Ответы |
Re[2]: [HACKERS] Execute ignoring cursor?
|
| Список | pgsql-hackers |
2016-04-11 13:11 GMT+02:00 nummervet nummervet <nummervet@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
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.stehule@gmail.com>:2016-04-08 16:46 GMT+02:00 nummervet nummervet <nummervet@mail.ru>:That didn't work for me:
ERROR: syntax error at or near "$"
LINE 1: ...ibute_id, set_id ) (select $."151", '...should be $1RegardsPavel
Пятница, 8 апреля 2016, 17:25 +03:00 от Pavel Stehule <pavel.stehule@gmail.com>:Hi2016-04-08 16:17 GMT+02:00 nummervet nummervet <nummervet@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-DYNRegardsPavel 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?
В списке pgsql-hackers по дате отправления: