Sunday, May 13, 2012

How To Add Quotes to results in dynamic SQL


I frequently find the need to have single quoted output when i generate dynamic SQL.

It's always a pain to remember the exact number of ticks needed to get the quoted output.

Here is a reminder on how to do it:
select ''''||schemaname||'.'||tablename||'''' from pg_tables
i.e., four quotes when there's no text, or three quotes on the outside and one on the inside when there is text.
select '''public.'||tablename||''';' from pg_tablesselect 'select pg_size_pretty(pg_relation_size('''||tablename||'''));' from pg_tables where schemaname = 'bla';

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Web Statistics