Обсуждение: BUG #4640: Drop leading zero in EXECUTE
The following bug has been logged online: Bug reference: 4640 Logged by: Eduard Deacoon Email address: deac@yandex.ru PostgreSQL version: all Operating system: FreeBSD Description: Drop leading zero in EXECUTE Details: Hi! EXECUTE has 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!
"Eduard Deacoon" <deac@yandex.ru> writes: > If $4 has leading zero it does`n work :( please expand on "doesn't work" > Context replace with ''0 || '' || $4 work only if $4 have one leading zero. > ''00 || '' || $4 does`n work, ''0 || 0 ||'' || $4 - work I'm sorry I'm not following this part. What parameters did you call this function with? What did you expect to happen? What actually happened? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!
05.02.09, 17:16, "Gregory Stark" <stark@enterprisedb.com>: > "Eduard Deacoon" <deac@yandex.ru> writes: > > If $4 has leading zero it does`n work :( > please expand on "doesn't work" > > Context replace with ''0 || '' || $4 work only if $4 have one leading zero. > > ''00 || '' || $4 does`n work, ''0 || 0 ||'' || $4 - work > I'm sorry I'm not following this part. What parameters did you call this > function with? What did you expect to happen? What actually happened? > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's 24x7 Postgres support! -- When $4 contain '0001' then have '1' When $4 contain '0000' then have NULL When string contain digits only with leading zero, then EXECUTE drop leading zero. Best regards!
On Thu, 5 Feb 2009, Eduard Deacoon wrote: > 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'' The output of that is going to look something like SELECT "Valueof$2" AS t FROM "Valueof$1" WHERE "Valueof$3" = Valueof$4::text ORDER BY "Valueof$2" ASC. So, given say a call with ('a', 'b', 'c', '003', ',') you'll get SELECT "b" AS t FROM "a" WHERE "c" = 003::text ORDER BY "b" ASC In that case, when executed the 003 is going to be treated as a number (and thus is the same as 3). If you wanted the 003 to be treated as a string literal in the string to be executed, you need to quote it, preferably with quote_literal.