471,108 Members | 1,263 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

Quotes in dynamic sql

A E
Hi,

I am trying to execute dynamic sql but I can't seem to get the qoutes right.

My code is

qry := ''SELECT * from ''|| trim(realname) ||'' where crmid like %''|| trim(crmid) ||''%'';

Can anyone help?

Alex

Nov 12 '05 #1
2 5272
Shouldn't it be:

qry := 'SELECT * from '|| trim(realname) ||' where crmid like ''%'||
trim(crmid) ||'%''';

The contents of qry should be:

'SELECT * from blahblah where crmid like '%crmid%'

Your errors (I think) are here:
qry := ''SELECT * from ''|| trim(realname) ||'' where crmid like %''||
^1 ^1 ^1 ^2

trim(crmid) ||''%'';
^1 ^3

^1 = only need a single quote
^2 = the quotes are after the % when they should be before
^3 = you have a missing closing single quote

Of course, if the above is in a pl/sql procedure, then you'll need to
double up all the quotes (I think).

Hope that helps.

John Sidney-Woollett

A E said:
Hi,

I am trying to execute dynamic sql but I can't seem to get the qoutes
right.

My code is

qry := ''SELECT * from ''|| trim(realname) ||'' where crmid like %''||
trim(crmid) ||''%'';

Can anyone help?

Alex

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #2
A E
I tried doubling the quotes because I am running a pl/pgsql procedure, did not work. Full code context:

begin
select into allowed secverf(username, talias);
if allowed = 0 then
RAISE NOTICE ''User not authorized to perform retrieve.'', allowed;
RETURN null;
else
select into realname tablename from applicationaliases where aliasname like ''%''|| trim(talias) ||''%'';
if length(crmid) = 0 then
qry := ''SELECT * from ''|| trim(realname);
FOR objectdefinition IN EXECUTE qry LOOP
RETURN NEXT objectdefinition;
END LOOP;
else
qry := ''SELECT * from ''|| trim(realname) ||'' where crmid like ''%''|| trim(crmid) ||''%'''';
FOR objectdefinition IN EXECUTE qry LOOP
RETURN NEXT objectdefinition;
END LOOP;
end if;
end if;
RETURN null;
end;'
LANGUAGE 'plpgsql' VOLATILE;

John Sidney-Woollett <jo****@wardbrook.com> wrote:
Shouldn't it be:

qry := 'SELECT * from '|| trim(realname) ||' where crmid like ''%'||
trim(crmid) ||'%''';

The contents of qry should be:

'SELECT * from blahblah where crmid like '%crmid%'

Your errors (I think) are here:
qry := ''SELECT * from ''|| trim(realname) ||'' where crmid like %''||
^1 ^1 ^1 ^2

trim(crmid) ||''%'';
^1 ^3

^1 = only need a single quote
^2 = the quotes are after the % when they should be before
^3 = you have a missing closing single quote

Of course, if the above is in a pl/sql procedure, then you'll need to
double up all the quotes (I think).

Hope that helps.

John Sidney-Woollett

A E said:
Hi,

I am trying to execute dynamic sql but I can't seem to get the qoutes
right.

My code is

qry := ''SELECT * from ''|| trim(realname) ||'' where crmid like %''||
trim(crmid) ||''%'';

Can anyone help?

Alex

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Cyrus D. | last post: by
11 posts views Thread by Jakanapes | last post: by
4 posts views Thread by (PeteCresswell) | last post: by
4 posts views Thread by Michael Yanowitz | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.