From: | "Eduard Deacoon" <deac(at)yandex(dot)ru> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4642: EXECUTE work incorrect |
Date: | 2009-02-05 12:11:51 |
Message-ID: | 200902051211.n15CBpIv073705@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 4642
Logged by: Eduard Deacoon
Email address: deac(at)yandex(dot)ru
PostgreSQL version: all
Operating system: FreeBSD
Description: EXECUTE work incorrect
Details:
Hi!
EXECUTE work incorrect if one or more string have leading zero.
For example:
--- Function convert column to string with delimiter
--- $1 - TABLE with COLUMN to convert
--- $2 - COLUMN to convert
--- $3 - COLUMN for WHERE CLAUSE
--- $4 - WHERE value
--- $5 - delimeter
--- In fact: SELECT $2 FROM $1 WHERE $3 = $4 returning as string with
delimiter $5
CREATE OR REPLACE FUNCTION column_to_string(TEXT, TEXT, TEXT, TEXT, TEXT)
RETURNS TEXT AS
E'
DECLARE
string_res TEXT := NULL;
r RECORD;
BEGIN
FOR r IN EXECUTE ''SELECT '' || QUOTE_IDENT($2) || '' AS t '' || ''
FROM '' || QUOTE_IDENT($1) || ''
WHERE '' || QUOTE_IDENT($3) || '' = '' ||
$4||''::TEXT'' ||
'' ORDER BY '' || QUOTE_IDENT($2) || '' ASC''
LOOP
IF r.t IS NOT NULL AND string_res IS NULL THEN
string_res := r.t;
ELSEIF r.t IS NOT NULL AND string_res IS NOT NULL THEN
string_res := string_res || $5 || r.t;
END IF;
END LOOP;
RETURN string_res;
END;
' LANGUAGE 'plpgsql' STABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
If $4 has leading zero it does`n work :(
Context replace with ''0 || '' || $4 work only if $4 have one leading zero.
''00 || '' || $4 does`n work, ''0 || 0 ||'' || $4 - work
Nice day!
From | Date | Subject | |
---|---|---|---|
Next Message | hubert depesz lubaczewski | 2009-02-05 12:22:54 | Re: BUG #4642: EXECUTE work incorrect |
Previous Message | Adrian Sandor | 2009-02-05 08:14:57 | BUG #4641: Wrong estimates for multi-column conditions |