I want to be able to get a valid SQL literal for any type and value.

If the type is text, varchar, etc... and the value is abc I want to get 'abc'.

If the type is timestamp I want quoted and formatted timestamp... Is there a builtin function for this in PostgreSQL or do I have to build one myself?

I'm doing the case thing right now, but I'm afraid that I'll miss some kind of edge case. I can't find an exhaustive list of all possible value literals in PostgreSQL.

I'm trying to generate a CREATE AGGREGATE statement and the problem is that I can't properly format the INITCOND value for every type. I have the type and the value... here is my query:

SELECT format('CREATE AGGREGATE %s (SFUNC = %s, STYPE = %s%s%s%s%s)',
         ', SORTOP = '    || NULLIF(a.aggsortop, 0)::regoper,
         ', INITCOND = '  || (case a.aggtranstype::regtype::text
                               when 'text' then '''' || a.agginitval || ''''
                               when 'varchar' then '''' || a.agginitval || ''''
                               when 'char' then '''' || a.agginitval || ''''
                               when 'timestamp' then '''' || a.agginitval || ''''
                               when 'date' then '''' || a.agginitval || ''''
                               when 'time' then '''' || a.agginitval || ''''
                               else a.agginitval end),
         ', FINALFUNC = ' || NULLIF(aggfinalfn, 0),
         CASE WHEN aggfinalextra THEN ', FINALFUNC_EXTRA' END
) AS ddl_agg
FROM pg_aggregate a
join pg_proc p  on a.aggfnoid = p.oid
join pg_namespace n ON p.pronamespace = n.oid
where n.nspname = 'public'

With quote_literal you can get a text representation of any type. Or if your input can be NULL quote_nullable might be more appropriate depending on what you wanna do with it.

select quote_literal(now());
'2018-08-01 16:27:42.696751+02'
(1 row)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.