Postgresql – Should Postgres function return/coerce numeric scale/precision

datatypesfunctionsplpgsqlpostgresql

The following function returns two generic numerics, not the numeric(20,10) I think I'm specifying in the function return definition. This is unexpected (by me). Is there a way to ensure the function does the "coercion" to numeric(20,10) so outside code doesn't have to cast?

drop function test_numeric;
CREATE OR REPLACE FUNCTION test_numeric ()
RETURNS TABLE(m numeric(20,10), n numeric(20,10)) AS $$
-- RETURNS TABLE(m numeric, n numeric) AS $$
BEGIN
    RETURN QUERY
    select
        -- returned as written (with more than 10 decimal places)
        88888888.88888888888888888 as mm,
        -- truncated as expected
        88888888.88888888888888888::numeric(20,10) as nn;
END;
$$ LANGUAGE plpgsql;

[Edit based on feedback in answers] Here's a fiddle to show how the function signature does not enforce the return type (hoped for). M is the point. Even though the signature says we're returning a numeric(20,10) a generic numeric gets returned. The problem is when subsequent code expects a numeric(20,10) but the type is numeric (comes in when doing a create or update view/table etc). What's most unexpected is that the function signature basically gets ignored, or overridden by the code, to the next user down the road sees the function signature, expects a 20/10 and gets something totally different.

Fiddle

Another fiddle showing it doesn't matter at all what scale or precision is specified to be returned; any numeric scale/precision can be returned and will remain the scale or precision it was inside the function body.

Fiddle

[Further Edit] Apologies for beating the horse and demonstrating my misunderstanding of the Numeric type perhaps. But if the scale and precision matters when changing this view, it's hard to understand why it wouldn't matter when defining the table the function will return. I could maybe understand if numeric(20,10) was a subtype of numeric and could be passed if I'd specified numeric to be returned. Just never heard of something less specific passing for something specified to be more specific (in a strongly typed language).

Fiddle

Best Answer

RETURNS TABLE(m numeric(20,10), n numeric(20,10)) does not enforce the data types of the returned columns; it's simply a declaration, metadata intended to inform the callers what the function should return. The declaration cannot not require anything, it's a "promise" that is to be fulfilled by the function implementation. The function code itself is responsible for what it returns. In other words, you must use explicit type casts, as necessary, in the function body.

If the function doesn't fulfil the promise, it's an implementation bug.

P.S. Looking at the source code of coerce_type, it simply compares type OIDs, so any numeric is compatible with any other numeric:


    if (targetTypeId == inputTypeId ||
        node == NULL)
    {
        /* no conversion needed */
        return node;
    }

Related Solutions

Sql-server – PostgreSQL equivalent of SQL Server SQL_VARIANT_PROPERTY

The fact that pg_typeof doesn't show the typmod is frustrating.

To get the fully qualified type you can query the system catalogs. Let's look at how psql does it using psql -E:

$ psql -E regress
psql (9.2.1)
Type "help" for help.

regress=> CREATE TABLE typmodtest ( a numeric(16,2), b varchar(32) );
CREATE TABLE
regress=> \d typmodtest

... tons of information printed, including this query which produces the information we want:

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
  NULL AS indexdef,
  NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '24641' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

This query can clearly be trimmed if you're not interested in the field collation, NOT NULL status, etc. We can also change the attrelid filter from an oid to a regclass filter:

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'typmodtest'::regclass 
  AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

producing:

 attname |      format_type      
---------+-----------------------
 a       | numeric(16,2)
 b       | character varying(32)

This will only work on relations. I'm not aware of a reliable, generic way to get the typmod-qualified type for any result set, and it's a serious irritation. Among other things it makes it hard for the PostgreSQL JDBC driver to be compliant.

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;

Best Answer

Related Solutions

Sql-server – PostgreSQL equivalent of SQL Server SQL_VARIANT_PROPERTY

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

DISTINCT ON()

Don't forget ORDER BY

Using an Ordered-Set Aggregate Function

Related Question

`DISTINCT ON()`

Don't forget `ORDER BY`