Обсуждение: syntax error with execute
I have a query like this in a plpgsql function: EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES ('||vals||') RETURNING currval('''||seqname||''') INTO newid' and I get the response: ERROR: syntax error at or near "INTO" LINE 1: ...','2008','4',NULL) RETURNING currval('id_seq') INTO newid And I do not understand this error. If I take the INSERT command and run it by hand, it works fine, but it doesn't work in the function when called by execute. Anybody has an idea on what is wrong and what to do about it?
Pretty sure you have to create the query as a string and execute that. On Fri, May 30, 2008 at 9:55 AM, A B <gentosaker@gmail.com> wrote: > I have a query like this in a plpgsql function: > > EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES > ('||vals||') RETURNING currval('''||seqname||''') INTO newid' > > and I get the response: > > ERROR: syntax error at or near "INTO" > LINE 1: ...','2008','4',NULL) RETURNING currval('id_seq') INTO newid > > And I do not understand this error. If I take the INSERT command and > run it by hand, it works fine, but it doesn't work in the function > when called by execute. Anybody has an idea on what is wrong and what > to do about it? > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
> EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES
> ('||vals||') RETURNING currval('''||seqname||''') INTO newid'
You probably were to do:
EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES
('||vals||') RETURNING currval('''||seqname||''')' INTO newid
Note where last quote goes.
I haven't use the RETURNING clause before, but the "INTO" option, at least in SELECT sentences, must be outside of the string expression. This way: EXECUTE 'some query' INTO variable;
Cheers.
Cheers.
2008/5/30 A B <gentosaker@gmail.com>:
I have a query like this in a plpgsql function:
EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES
('||vals||') RETURNING currval('''||seqname||''') INTO newid'
and I get the response:
ERROR: syntax error at or near "INTO"
LINE 1: ...','2008','4',NULL) RETURNING currval('id_seq') INTO newid
And I do not understand this error. If I take the INSERT command and
run it by hand, it works fine, but it doesn't work in the function
when called by execute. Anybody has an idea on what is wrong and what
to do about it?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, May 30, 2008 at 9:25 PM, A B <gentosaker@gmail.com> wrote:
The final INTO clause should be outside the string, like this:
EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES
('||vals||') RETURNING currval('''||seqname||''')' INTO newid
Note the placement of the last quote.
HTH,
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
I have a query like this in a plpgsql function:
EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES
('||vals||') RETURNING currval('''||seqname||''') INTO newid'
and I get the response:
ERROR: syntax error at or near "INTO"
LINE 1: ...','2008','4',NULL) RETURNING currval('id_seq') INTO newid
And I do not understand this error. If I take the INSERT command and
run it by hand, it works fine, but it doesn't work in the function
when called by execute. Anybody has an idea on what is wrong and what
to do about it?
The final INTO clause should be outside the string, like this:
EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES
('||vals||') RETURNING currval('''||seqname||''')' INTO newid
Note the placement of the last quote.
HTH,
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
> EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES > ('||vals||') RETURNING currval('''||seqname||''')' INTO newid > > Note where last quote goes. That was exactly what I wanted to do! SELECT 'Thank you' FROM heart;