CODEWITHSUNDEEP
sqlpostgresqlstored-procedures
Wug
Wug

Reputation: 13196

Error in PostgreSQL stored procedure that returns a table

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.

Upvotes: 3

Views: 8102

Answers (1)

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95761

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.

Upvotes: 4

Related Questions