Execute ignoring cursor?

Started by nummervet nummervetabout 10 years ago8 messageshackers

nummervet nummervet

nummervet@mail.ru

about 10 years ago

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?

pavel.stehule@gmail.com

about 10 years ago

In reply to: nummervet nummervet (#1)

Re: Execute ignoring cursor?

Hi

2016-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-DYN

Regards

Pavel Stehule

Show quoted text

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?

nummervet nummervet

nummervet@mail.ru

about 10 years ago

In reply to: Pavel Stehule (#2)

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", '...

Show quoted text

Пятница, 8 апреля 2016, 17:25 +03:00 от Pavel Stehule <pavel.stehule@gmail.com>:

Hi

2016-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-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?

pavel.stehule@gmail.com

about 10 years ago

In reply to: nummervet nummervet (#3)

Re: Execute ignoring cursor?

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 $1

Regards

Pavel

Show quoted text

Пятница, 8 апреля 2016, 17:25 +03:00 от Pavel Stehule <
pavel.stehule@gmail.com>:

Hi

2016-04-08 16:17 GMT+02:00 nummervet nummervet <nummervet@mail.ru
<https://e.mail.ru/compose/?mailto=mailto%3anummervet@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?

nummervet nummervet

nummervet@mail.ru

about 10 years ago

In reply to: Pavel Stehule (#4)

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?

Show quoted text

Пятница, 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 $1

Regards

Pavel

Пятница, 8 апреля 2016, 17:25 +03:00 от Pavel Stehule < pavel.stehule@gmail.com >:

Hi

2016-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-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?

pavel.stehule@gmail.com

about 10 years ago

In reply to: nummervet nummervet (#5)

Re: Execute ignoring cursor?

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

Regards

Pavel

Show quoted text

Пятница, 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
<https://e.mail.ru/compose/?mailto=mailto%3anummervet@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.stehule@gmail.com
<https://e.mail.ru/compose/?mailto=mailto%3apavel.stehule@gmail.com>>:

Hi

2016-04-08 16:17 GMT+02:00 nummervet nummervet <nummervet@mail.ru
<https://e.mail.ru/compose/?mailto=mailto%3anummervet@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?

nummervet nummervet

nummervet@mail.ru

about 10 years ago

In reply to: Pavel Stehule (#6)

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 :)

Show quoted text

Понедельник, 11 апреля 2016, 16:10 +03:00 от Pavel Stehule <pavel.stehule@gmail.com>:

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

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 $1

Regards

Pavel

Пятница, 8 апреля 2016, 17:25 +03:00 от Pavel Stehule < pavel.stehule@gmail.com >:

Hi

2016-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-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?

pavel.stehule@gmail.com

about 10 years ago

In reply to: nummervet nummervet (#7)

Re: Execute ignoring cursor?

2016-04-11 16:31 GMT+02:00 nummervet nummervet <nummervet@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

Show quoted text

Понедельник, 11 апреля 2016, 16:10 +03:00 от Pavel Stehule <
pavel.stehule@gmail.com>:

2016-04-11 13:11 GMT+02:00 nummervet nummervet <nummervet@mail.ru
<https://e.mail.ru/compose/?mailto=mailto%3anummervet@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.stehule@gmail.com
<https://e.mail.ru/compose/?mailto=mailto%3apavel.stehule@gmail.com>>:

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

Hi

2016-04-08 16:17 GMT+02:00 nummervet nummervet <nummervet@mail.ru
<https://e.mail.ru/compose/?mailto=mailto%3anummervet@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?