structure of query does not match function result type

Question

After changing type (by casting) from bigint to text my PLPGSQL function stopped working. This is the error I am getting:

dev=> select * from app.get_companies(4,808739954140037) ;
NOTICE:  Data rows were NOT found (structure of query does not match function result type)
 company_id_str | company_name 
----------------+--------------
(0 rows)

dev=>

And this is my function:

CREATE OR REPLACE FUNCTION app.get_companies(ident_id bigint,sess bigint)
RETURNS TABLE(company_id_str text,company_name text) as $$
DECLARE
    server_session bigint;
BEGIN
    select app.session.session from app.session where app.session.identity_id=ident_id and app.session.session=sess into server_session;
    IF FOUND
    THEN
        BEGIN
            RETURN QUERY SELECT quote_ident(app.company.company_id::text)::text as company_id_str,app.company.name as company_name FROM app.company,app.identcomp WHERE app.company.company_id=app.identcomp.company_id and app.identcomp.identity_id=ident_id;
        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE 'Data rows were NOT found (%)',SQLERRM;
                RETURN;

        END;
    ELSE
        RAISE NOTICE 'Session row was NOT found';
        RETURN;
    END IF;
END;
$$ LANGUAGE plpgsql;

Why is this error happening if I am applying the cast and I am defining the output as TABLE ?

The app.company table is defined as:

CREATE TABLE app.company (
  company_id BIGSERIAL,
  date_inserted TIMESTAMP,
  name VARCHAR(64)
);

klin · Accepted Answer · 2016-12-18 05:09:03Z

11

app.company.name is varchar(64) while company_name of returned table is text. Cast app.company.name to text.

If you do not catch an exception in the function body (what for?) you would get more verbose error message:

ERROR:  structure of query does not match function result type
DETAIL:  Returned type character varying(64) does not match expected type text in column 2.

answered Dec 18, 2016 at 5:09

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Nulik Over a year ago

I thought varchar and text were compatible. Thanks!

Bharat Suryawanshi · Accepted Answer · 2018-10-29 07:26:22Z

1

If you are changing return type of existing function, Then you need to drop the existing function and again you have to create the same function.

To drop the function :

DROP FUNCTION [ IF EXISTS ] name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] [, ...]
[ CASCADE | RESTRICT ]

answered Oct 29, 2018 at 7:26

Bharat Suryawanshi

614 bronze badges

Comments

blackgreen · Accepted Answer · 2024-02-19 11:15:42Z

0

I got the same error below:

ERROR: structure of query does not match function result type

When I created and called my_func() below which returns a set of rows with a RETURN QUERY statement.

CREATE FUNCTION my_func()
RETURNS TABLE(id INT, name TEXT)
AS $$
BEGIN
  RETURN QUERY VALUES 
    (1::INT, 'John'::VARCHAR(20)), 
    (2::INT, 'David'::VARCHAR(20));
END;
$$ LANGUAGE plpgsql;

So, I changed TEXT to VARCHAR(20) as shown below, then the error was solved. *VARCHAR also works:

CREATE FUNCTION my_func()
RETURNS TABLE(id INT, name VARCHAR(20))
AS $$                  -- `VARCHAR` also works.
BEGIN
  RETURN QUERY VALUES 
    (1::INT, 'John'::VARCHAR(20)), 
    (2::INT, 'David'::VARCHAR(20));
END;
$$ LANGUAGE plpgsql;

edited Feb 19, 2024 at 11:15

blackgreen♦

46.9k29 gold badges171 silver badges162 bronze badges

answered Feb 6, 2024 at 19:50

Super Kai - Kazuya Ito

42.8k23 gold badges258 silver badges257 bronze badges

Collectives™ on Stack Overflow

structure of query does not match function result type

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related