1

Here is a yet unanswered second part of an earlier question as a separate question:

CREATE OR REPLACE FUNCTION f1 (a1 INTEGER, a2 INTEGER)
RETURNS TABLE (c1 VARCHAR(20), c2 VARCHAR(20), c3 INTEGER, c4 VARCHAR(20), c5 VARCHAR(128))
AS $$
SELECT 'a'::VARCHAR(20), 'b'::VARCHAR(20), 1::INTEGER, 'c'::VARCHAR(20), 'd'::VARCHAR(128);
$$ LANGUAGE SQL;

This sample code is meant to create a function f1 that returns a table with a single row of 5 columns.

If I remove the casts from the SELECT statement, I get this error message:

Final statement returns unknown instead of character varying at column 1.

But are the casts really necessary? It would seem that the correct type VARCHAR(20) is known from RETURNS, so why is there no implicit cast of strings such as 'a'? Is there a better way to formulate such things in a concise way?

Community
  • 1
  • 1
Drux
  • 11,992
  • 13
  • 66
  • 116
  • 1
    Any reason you aren't just using `TEXT`? I believe that would also resolve the implicit type conversion for you. – Nick Aug 05 '16 at 18:21
  • postgres does not know if you don't try to select 'a'::int or 'a'::float or anything else is next rows or so => type is `unknown` – Vao Tsun Aug 05 '16 at 18:32
  • 1
    Some relatively old discussion about this: https://www.postgresql.org/message-id/183.1302200970%40sss.pgh.pa.us – Nick Aug 05 '16 at 18:41

0 Answers0