Postgresql 9.6 Error trying to have a function return a string without first casting it as a string.

datatypesfunctionspostgresqlpostgresql-9.6

I am in the progress of learning PostgreSQL (9.6) and am a bit confused by an error I am receiving. If I attempt to create the following function I I receive the following error.

create or replace FUNCTION HelloWorld ()
RETURNS TABLE(HelloCol text, WorldCol text) AS $$

SELECT 'Hello' , 'World' ;
$$
 LANGUAGE sql;

Error:

    ERROR:  return type mismatch in function declared to return record
DETAIL:  Final statement returns unknown instead of text at column 1.
CONTEXT:  SQL function "helloworld"
SQL state: 42P13

If I cast the text as text it works properly.

create or replace FUNCTION HelloWorld ()
RETURNS TABLE(HelloCol text, WorldCol text) AS $$

SELECT cast('Hello' as text) , cast('World' as Text);
$$
 LANGUAGE sql;

I've also tried char(10) and varchar(10) in the first scenario with the same error. I assume I'm just not getting the right variable type in the "TABLE(HelloCol text, WorldCol text)" portion but if 'Hello' isn't text, char, or varchar, what is it? Just some undefined type?

I can change the returned columns to int and have it Select 1,2 and that works just fine without casting.

Best Answer

This has been fixed in version 10, as seen in the docs, Type Conversions:

SELECT Output Columns

The rules given in the preceding sections will result in assignment of non-unknown data types to all expressions in a SQL query, except for unspecified-type literals that appear as simple output columns of a SELECT command. For example, in
 SELECT 'Hello World';
there is nothing to identify what type the string literal should be taken as. In this situation PostgreSQL will fall back to resolving the literal's type as text.

...

Note
Prior to PostgreSQL 10, this rule did not exist, and unspecified-type literals in a SELECT output list were left as type unknown. That had assorted bad consequences, so it's been changed.

The solutions for you are pretty straight forward:

Either cast the values to text or upgrade to version 10.

`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;

Sql-server – Return text from PostgreSQL (9.6) to SQL Server from linked server query

ODBC Problem

I think this is a function of the PostgreSQL ODBC driver

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL]
"MaxLongVarcharSize"="4094"

Changing that with regedit may work (no tabs or spaces around =). However, there may be other drawbacks.

This is also documented in the psqlODBC Configuration Options..

Max LongVarChar: The maximum precision of the LongVarChar type. The default is 4094 which actually means 4095 with the null terminator. You can even specify (-4) for this size, which is the odbc SQL_NO_TOTAL value.

I believe setting this to SQL_NO_TOTAL (ie., -4) means that the ODBC driver has to malloc rather static allocate the col size.

GIS sub-optimal

From the PostGIS angle, you should be using Well-Known-Binary via ST_AsBinary and not storing the WKT as nvarchar, but instead storing the geometry from WKB in SQL Server.

Best Answer

Related Solutions

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

DISTINCT ON()

Don't forget ORDER BY