Re: [HACKERS] Execute ignoring cursor?

[Pavel Stehule]

2016-04-11 16:31 GMT+02:00 nummervet nummervet :

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

[HACKERS] Re[2]: [HACKERS] Execute ignoring cursor?

[nummervet nummervet]

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

>>>
>>
>

Re: [HACKERS] Execute ignoring cursor?

[Pavel Stehule]

2016-04-11 13:11 GMT+02:00 nummervet nummervet :

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

[HACKERS] Re[4]: [HACKERS] Execute ignoring cursor?

[nummervet nummervet]

 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?


>Пятница,  8 апреля 2016, 18:24 +03:00 от Pavel Stehule 
>:
>
>
>
>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?

>>>
>>
>

Re: [HACKERS] Execute ignoring cursor?

[Pavel Stehule]

2016-04-08 16:46 GMT+02:00 nummervet nummervet :

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

[HACKERS] Re[2]: [HACKERS] Execute ignoring cursor?

[nummervet nummervet]

 That didn't work for me:

ERROR:  syntax error at or near "$"
LINE 1: ...ibute_id, set_id ) (select $."151", '...


>Пятница,  8 апреля 2016, 17:25 +03:00 от Pavel Stehule 
>:
>
>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?
>>
>

Re: [HACKERS] Execute ignoring cursor?

[Pavel Stehule]

Hi

2016-04-08 16:17 GMT+02:00 nummervet nummervet :

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

[HACKERS] Execute ignoring cursor?

[nummervet nummervet]

 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?