From: | A E <cooljoint(at)yahoo(dot)com> |
---|---|
To: | johnsw(at)wardbrook(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Quotes in dynamic sql |
Date: | 2003-12-30 20:11:59 |
Message-ID: | 20031230201159.74301.qmail@web12107.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <johnsw(at)wardbrook(dot)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 majordomo(at)postgresql(dot)org)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-12-30 20:21:46 | Re: 'like' refuses to use an index??? |
Previous Message | John Sidney-Woollett | 2003-12-30 19:56:35 | Re: Quotes in dynamic sql |