Обсуждение: cvs text to quoted cvs text
'select val from mytab where zzz in ('||csv_input_parm::text||')';
[THIS,THAT,THE OTHER] while the IN list would need
['THIS','THAT','THE OTHER'] brackets not included.
select ''''||array_to_string(string_to_array('THIS,THAT,THE OTHER',','),''',''')||'''' rslt
Regards,
Igor
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Michael Moore
Sent: Monday, September 26, 2016 3:23 PM
To: postgres list <pgsql-sql@postgresql.org>
Subject: [SQL] cvs text to quoted cvs text
I have some input parameters on a function that will by use in dynamic sql as part of an IN list. For example:
'select val from mytab where zzz in ('||csv_input_parm::text||')';
The problem is that csv_input_parm is formatted like:
[THIS,THAT,THE OTHER] while the IN list would need
['THIS','THAT','THE OTHER'] brackets not included.
I came up with this approach to do the conversion:
select ''''||array_to_string(string_to_array('THIS,THAT,THE OTHER',','),''',''')||'''' rslt
It gets the job done, but it's ugly. Is there a way that is not ugly?
thanks,
Mike
Take a look at quote_literal(…) function.
Regards,
Igor Neyman
I have some input parameters on a function that will by use in dynamic sql as part of an IN list. For example:
'select val from mytab where zzz in ('||csv_input_parm::text||')';The problem is that csv_input_parm is formatted like:
[THIS,THAT,THE OTHER] while the IN list would need
['THIS','THAT','THE OTHER'] brackets not included.I came up with this approach to do the conversion:
select ''''||array_to_string(string_to_array('THIS,THAT,THE OTHER',','),''',''')||'''' rslt It gets the job done, but it's ugly. Is there a way that is not ugly?
postgres=# DO $$
DECLARE x text[] = '{AHOJ,NAZDAR}'; r text;
BEGIN
FOR r IN EXECUTE 'SELECT UNNEST($1)' USING x
LOOP
RAISE NOTICE '>>>%<<<', r;
END LOOP;
END;
$$;
NOTICE: >>>AHOJ<<<
NOTICE: >>>NAZDAR<<<
DO
thanks,Mike
Igor, not seeing how quote_literal will quote each element of the csv. It will quote the entire string, but not each element.
SELECT string_agg(quote_literal(v),',') from unnest('{AHOJ,NAZDAR}'::text[]) g(v);
Pavel, Interesting but unfortunately my dynamic sql is very complex, hundreds of lines, and a undetermined number of USING variables would be needed.Thanks!
2016-09-26 22:09 GMT+02:00 Michael Moore <michaeljmoore@gmail.com>:Igor, not seeing how quote_literal will quote each element of the csv. It will quote the entire string, but not each element.
SELECT string_agg(quote_literal(v),',') from unnest('{AHOJ,NAZDAR}'::text[] ) g(v); Pavel, Interesting but unfortunately my dynamic sql is very complex, hundreds of lines, and a undetermined number of USING variables would be needed.Thanks!