Function in postgres with return many rows with some null value

Question

i have little problem with function in postgress means. I have function:

CREATE OR REPLACE FUNCTION test(
    x integer,
    y character varying,
    z character varying
)
 RETURNS TABLE(x1 int, y1 varchar,n1 varchar,z1 varchar) AS
$BODY$
BEGIN
   RETURN QUERY
SELECT  x,y,null,z  FROM example_tab            
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

When I execute this function I take error:

 Error: Returned type unknown does not match expected type character varying in column 3.

 SELECT * FROM test(694531020,'t'::varchar,'t'::varchar)

What should I do to correct run this query and execute this function ???

SELECT x,y,null::varchar,z FROM example_tab and please remove mysql and sql-server from tags — Vao Tsun
– Vao Tsun, Commented Jul 4, 2017 at 8:58

Vao Tsun · Accepted Answer · 2017-07-04 09:12:30Z

3

You should explicitly specify datatype for null, like:

CREATE OR REPLACE FUNCTION test(
    x integer,
    y character varying,
    z character varying
)
 RETURNS TABLE(x1 int, y1 varchar,n1 varchar,z1 varchar) AS
$BODY$
BEGIN
   RETURN QUERY
SELECT  x,y,null::varchar,z  FROM example_tab            
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

answered Jul 4, 2017 at 9:12

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

2 Comments

volume one Over a year ago

What is the logic in casting null (an unknown value) as an integer?

Vao Tsun Over a year ago

to match types of return table I suppose

Collectives™ on Stack Overflow

Function in postgres with return many rows with some null value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related