1

I'm trying to do my first Function in PostgreSQL, but I can't to do it. I was write this piece of code

CREATE OR REPLACE FUNCTION getCars () 
   RETURNS TABLE (
      car_name VARCHAR,
      id_car INT
) 
AS $$
BEGIN
   RETURN QUERY SELECT
      brand,car_id
   FROM
      cars;
end;
$$ 

LANGUAGE plpgsql;

But... The error is when I'm trying to call taht function:

SELECT getcars();

Where is the issue?

the error message it's:

SQL Error [42804]: ERROR: structure of query does not match function result type
  Detail: Returned type text does not match expected type character varying in column 1.
  Where: PL/pgSQL function getcars() line 3 at RETURN QUERY

Thanks in advance!

2

You have two errors:

  1. apparently brand is defined as text, not varchar
  2. set returning functions have to be used like a table in the FROM clause.

So you need to change your function definition to:

CREATE OR REPLACE FUNCTION getcars() 
   RETURNS TABLE (car_name text, id_car INT) 
AS $$
BEGIN
   RETURN QUERY 
   SELECT brand,car_id
   FROM cars;
end;
$$ 
LANGUAGE plpgsql;

And then use it like this:

select *
from getcars();

Simple functions that just return a query are better defined as language sql:

CREATE OR REPLACE FUNCTION getcars() 
   RETURNS TABLE (car_name text, id_car INT) 
AS $$
   SELECT brand,car_id
   FROM cars;
$$ 
LANGUAGE sql
stable;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.