How to get a SQL value literal for arbitrary type in PostgreSQL? 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)',
         a.aggfnoid::regprocedure,
         a.aggtransfn,
         a.aggtranstype::regtype,
         ', 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());
          quote_literal
---------------------------------
'2018-08-01 16:27:42.696751+02'
(1 row)