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

Question

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.

• ypercubeᵀᴹ: Just cast them, as you did. The string literals (like 'Hello') and not automatically converted to text (or varchar) by Postgres. Because these literals can be text or dates or other types.
• McNets: You can use ::text instead of cast() SELECT 'Hello'::text , 'World'::text ;

Answer 1

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.

• BGCDC: Thanks this was informative. I will likely upgrade to version 10 in the future. I wanted to try out DBForge Studio which currently only supports up to 9.6.