Error in PostgreSQL stored procedure that returns a table

Question

I'm trying to write a stored procedure that returns a table. The procedure is syntactically correct and psql accepts it, but it throws runtime errors.

What I have so far:

CREATE OR REPLACE FUNCTION get_todays_appointments()
RETURNS TABLE 
(
    fname VARCHAR(32),
    lname VARCHAR(32),
    phoneno CHAR(10),
    datetime TIMESTAMP WITHOUT TIME ZONE,
    duration INTERVAL,
    caseid INTEGER
) AS $$
BEGIN
    RETURN QUERY
        SELECT
        (
            client.fname, 
            client.lname, 
            client.phoneno, 
            appointment.datetime, 
            appointment.duration, 
            photocase.caseid
        )
            FROM  (appointment NATURAL JOIN photocase NATURAL JOIN client)
            WHERE 
            (
                appointment.datetime >= current_date 
                AND appointment.datetime < current_date + 1
            );
END;
$$
LANGUAGE plpgsql;

If I execute the query manually, it works exactly as indended, but using the SP I always run into the following error:

ERROR: structure of query does not match function result type DETAIL: Returned type record does not match expected type character varying in column 1. CONTEXT: PL/pgSQL function "get_todays_appointments" line 3 at RETURN QUERY

I've double checked the table schema about 15 times and theyre definitely correct.

The weird part is that the function works fine if I prune attributes so it returns only one at a time. As soon as I try to return more than one attribute, it throws the error.

I've googled and found some examples but nothing that actually works. I've also seen the use of SETOF, but there is no table with this signature so it doesn't really help me.

I'm using postgresql v9.1.7.

I don't see anything here that warrants using a function. Did you consider using a view instead? — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Jan 27, 2013 at 1:15
It's an academic exercise. There probably isn't anything to warrant using a function, but I'm supposed to use them somehow. Since I'm having trouble with it, it's probably a good thing im doing it. — Wug
– Wug, Commented Jan 27, 2013 at 1:30

Mike Sherrill 'Cat Recall' · Accepted Answer · 2013-01-27 13:06:18Z

4

I don't have a convenient way to test this right now, but I think you're going to have to lose some parens.

CREATE OR REPLACE FUNCTION get_todays_appointments()
RETURNS TABLE 
(
    fname VARCHAR(32),
    lname VARCHAR(32),
    phoneno CHAR(10),
    datetime TIMESTAMP WITHOUT TIME ZONE,
    duration INTERVAL,
    caseid INTEGER
) AS $$
BEGIN
    RETURN QUERY
        SELECT
            client.fname, 
            client.lname, 
            client.phoneno, 
            appointment.datetime, 
            appointment.duration, 
            photocase.caseid
            FROM  (appointment NATURAL JOIN photocase NATURAL JOIN client)
            WHERE 
            (
                appointment.datetime >= current_date 
                AND appointment.datetime < current_date + 1
            );
END;
$$
LANGUAGE plpgsql;

PostgreSQL's error messages are usually pretty good. This one is literally true.

ERROR: structure of query does not match function result type DETAIL: Returned type record does not match expected type character varying in column 1. CONTEXT: PL/pgSQL function "get_todays_appointments" line 3 at RETURN QUERY

In this case, RETURN QUERY returns a value of type "record". That's because a row constructor looks like this, SELECT ROW(value1, column1, column2). And in a SELECT statement, the keyword "ROW" is optional, so a row constructor looks like this: SELECT (value1, column1, column2).

So this skeleton syntax

select (column1, column2) from whatever

is equivalent to this.

select row(column1, column2) from whatever

But you don't want that. You want something equivalent to this.

select column1, column2 from whatever

So lose those parens around the column list.

edited Jan 27, 2013 at 13:06

answered Jan 27, 2013 at 1:24

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

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

5 Comments

Wug Over a year ago

I would never ever have caught this. Thanks for your explanation, it made a lot of sense. I read over the 'record'; would never have noticed it.

Mike Sherrill 'Cat Recall' Over a year ago

I just put in a link to the documentation.

Wug Over a year ago

I'm guessing that I'd be able to leave the parentheses if I changed the return type to SETOF RECORD?

Mike Sherrill 'Cat Recall' Over a year ago

I'm not sure. Why don't you try that and let me know? :-)

Wug Over a year ago

That one also throws runtime errors, but by the look of them it's "more correct" than the way I was doing it before.

Collectives™ on Stack Overflow

Error in PostgreSQL stored procedure that returns a table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related