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: 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.