How can I return a string literal from a Postgres function?

Question

I got this working to return an integer:

CREATE FUNCTION my_function() RETURNS INTEGER AS $$
  SELECT 1 AS result;
$$ LANGUAGE SQL;

But I want it to return a string, so I tweaked it to:

CREATE FUNCTION my_function() RETURNS TEXT AS $$
  SELECT '1' AS result;
$$ LANGUAGE SQL;

But this fails with:

ERROR:  return type mismatch in function declared to return text
DETAIL:  Actual return type is unknown.

What happened? How can I return the string '1'?

'1' by default is varchar. you need to specify explicit typecasting : CREATE FUNCTION my_function() RETURNS TEXT AS $$ SELECT '1'::TEXT AS result; $$ LANGUAGE SQL; — Ilya Dyoshin
– Ilya Dyoshin, Commented Jun 5, 2017 at 20:21
@IlyaDyoshin - Your comment is correct generally, but '1' by default shows unknown — Oto Shavadze
– Oto Shavadze, Commented Jun 5, 2017 at 20:33

Laurenz Albe · Accepted Answer · 2017-06-06 10:14:33Z

2

String literals are of type unknown. You can explicitly specify the type like this:

SELECT TEXT '1' AS result;

answered Jun 6, 2017 at 10:14

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1