Postgresql – How to get a SQL value literal for arbitrary type in PostgreSQL

postgresqlscripting

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'

Best Answer

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)

Related Solutions

Postgresql – Difficult categorising of rows

Ok, so my answer here is conditioned on the following assumptions:

This categorization needs to be relatively stable and
It may be re-used in various ways, and
It needs to perform well.

The obvious, simple answer is to use table methods in PostgreSQL. What you do is create a SQL language function which does not hit the table and returns the value you want. Make it immutable so you can index the output if you want to query against it, etc. Note the specific rules aren't really clear from your post but this should get you started.

CREATE OR REPLACE FUNCTION subcategory(task) -- task is table name
RETURNS text LANGUAGE sql IMMUTABLE AS
$$
    SELECT CASE WHEN $1.type = 3 THEN
                CASE WHEN $I.reference LIKE 'IX#%' 
                     THEN 'Company IX Chargeable'
                     ELSE 'Other Chargeable'
                END
                WHEN $1.reference LIKE 'IX#%' THEN 
                CASE WHEN $1.type = 5 THEN 'Company IX Rebool'
                     ELSE 'Company IX Other'
                END
                WHEN $1.reference LIKE 'THR#%' AND $1.type IN (1, 2) THEN
                CASE WHEN $1.type = 2 THEN 'THR Return to Base'
                     WHEN $1.type = 1 THEN 'THR On Site'
                END
                WHEN $1.type = 2 THEN 'Return to Base'
                ELSE 'Other Nonchargeable'
           END;
$$;

You can then query this using:

SELECT t.subcategory FROM task t;

Note that the table name is non-optional here. The parser converts this to:

SELECT subcategory(t) FROM task t;

You can use this however in any part of the select statement including the where clause. If it proves slow, you can add cost estimates (but I think this should be fast), and you can even index the output using PostgreSQL's functional indexes.

On the performance side, LIKE and substring() don't seem to be significantly different:

postgres=# select count(*) from generate_series(1, 10000000);
  count   
----------
 10000000
(1 row)

Time: 9007.618 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE s::text like '1%';
  count  
---------
 1111112
(1 row)

Time: 13653.000 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE substring(s::text from 1 for 1) = '1';
  count  
---------
 1111112
(1 row)

Time: 16681.860 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE s::text like '1%';
  count  
---------
 1111112
(1 row)

Time: 17163.470 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE substring(s::text from 1 for 1) = '1';
  count  
---------
 1111112
(1 row)

Time: 17052.004 ms

So sometimes one is faster than the other, but they don't seem out of line with eachother.

Postgresql – In PostgreSQL, is there a type-safe first() aggregate function

`DISTINCT ON()`

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget `ORDER BY`

Also, while I didn't bold it then I will now

Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)    
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)   
FROM foo
GROUP BY a,b;