Prepared Statement Does Not Exists, PostgreSQL-CodePudding

I created the stored procedure with this code

CREATE PROCEDURE get_conferences_for_attendee
(
  IN start_time TIMESTAMP,
  IN end_time TIMESTAMP,
  IN email VARCHAR(255),
  IN deleted BOOLEAN
)
AS
$$
  SELECT c.localuuid, c.title, i.id, i.start_time, i.end_time, i.status, a.email, a.deleted
  FROM Conference c
  INNER JOIN Instance i ON i.conference_localuuid = c.localuuid
  INNER JOIN Conference_Attendees ca ON ca.conference_localuuid = c.localuuid
  INNER JOIN Attendee a ON ca.attendees_localuuid = a.localuuid
  WHERE i.start_time BETWEEN start_time AND end_time
  AND a.email = email
  AND a.deleted = deleted
$$ LANGUAGE SQL;

and this returned

CREATE PROCEDURE

I can see my procedure

SELECT proname, prorettype
FROM pg_proc
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');


           proname            | prorettype 
------------------------------ ------------
 get_conferences_for_attendee |       2278

When I try to execute, I get the error on the title.

EXECUTE get_conferences_for_attendee ('2022-12-26T00:00:00', '2023-01-01T23:59:59', '[email protected]', false);

ERROR: prepared statement "get_conferences_for_attendee" does not exist

Update

I found a solution but I'm not sure if it's the proper way to create this. It looks too complicated for me.

CREATE TYPE conference_record AS (                                                                                     
  localuuid VARCHAR(255),
  title VARCHAR(255),
  id VARCHAR(255),
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  status VARCHAR(255),
  email VARCHAR(255),
  deleted BOOLEAN
);

CREATE FUNCTION get_conferences_for_attendee
(
  IN start_time TIMESTAMP,
  IN end_time TIMESTAMP,
  IN email VARCHAR(255),
  IN deleted BOOLEAN
)
RETURNS SETOF conference_record AS $$
  BEGIN
    RETURN QUERY
    SELECT c.localuuid, c.title, i.id, i.start_time, i.end_time, i.status, a.email, a.deleted
    FROM Conference c
    INNER JOIN Instance i ON i.conference_localuuid = c.localuuid
    INNER JOIN Conference_Attendees ca ON ca.conference_localuuid = c.localuuid
    INNER JOIN Attendee a ON ca.attendees_localuuid = a.localuuid
    WHERE i.start_time BETWEEN $1 AND $2
    AND a.email = $3
    AND a.deleted = $4;
  END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_conferences_for_attendee ('2022-12-26T00:00:00', '2023-01-01T23:59:59', '[email protected]', false);

CodePudding user response：

As pointed out in the comments, to use a procedure, you need to CALL your_procedure();.
The code you presented looks like you're trying to get something from it, so a function is more suitable - procedures can return data through out and inout parameters or side-effects, like dumping them to an outside table.
The function and type definitions you later added in an edit look fine. If you're planning to feed it directly into a table, you don't need to define the custom type and instead specify RETURNS SETOF your_target_table_name or RETURNS TABLE (LIKE your_target_table_name).

You can also make it LANGUAGE sql - since you're not using anything plpgsql-specific, you don't need the additional overhead that comes with it. You'll just have to remove BEGIN RETURN QUERY and END, leaving just the bare-bones query.
You can also use a regular prepared statement for this:

PREPARE get_conferences_for_attendee(
           TIMESTAMP,
           TIMESTAMP,
           VARCHAR(255),
           BOOLEAN                  ) AS
SELECT 
    c.localuuid, 
    c.title, 
    i.id, 
    i.start_time, 
    i.end_time, 
    i.status, 
    a.email, 
    a.deleted
FROM Conference c
INNER JOIN Instance i               ON i.conference_localuuid = c.localuuid
INNER JOIN Conference_Attendees ca  ON ca.conference_localuuid = c.localuuid
INNER JOIN Attendee a               ON ca.attendees_localuuid = a.localuuid
WHERE i.start_time BETWEEN $1 AND $2
AND a.email = $3
AND a.deleted = $4;

And use it exactly like you intially planned to, with an EXECUTE:

EXECUTE get_conferences_for_attendee(
           '2022-12-26T00:00:00', 
           '2023-01-01T23:59:59', 
           '[email protected]', 
           false);

Online demo

CodePudding user response：

I found a solution but I'm not sure if it's the proper way to create this.

A function is the correct way to do this.

It looks too complicated for me.

You are indeed over-complicating the implementation. You don't need to create a type, this can be simplified by using returns table() instead.

You also don't need PL/pgSQL for this. A SQL function will be enough

CREATE FUNCTION get_conferences_for_attendee
(
  p_start_time TIMESTAMP,
  p_end_time TIMESTAMP,
  p_email text,
  p_deleted BOOLEAN
)
RETURNS table(localuuid text, title, text, id text, start_time timestamp, end_time timestamp, status text, email text, deleted boolean)
AS 
$$
    SELECT c.localuuid, c.title, i.id, i.start_time, i.end_time, i.status, a.email, a.deleted
    FROM Conference c
    INNER JOIN Instance i ON i.conference_localuuid = c.localuuid
    INNER JOIN Conference_Attendees ca ON ca.conference_localuuid = c.localuuid
    INNER JOIN Attendee a ON ca.attendees_localuuid = a.localuuid
    WHERE i.start_time BETWEEN p_start_time AND p_end_time
    AND a.email = p_email
    AND a.deleted = p_deleted
$$ 
LANGUAGE sql
stable;

I renamed the parameters with a prefix to avoid a name clash with columns of the same name.

Note that using BETWEEN with timestamp values is usually a bad idea. It's better to use a range query using >= for the lower bound and < for the "next day" of the upper bound

e.g. start_time >= 2022-12-26 00:00:00' and end_time < '2023-01-02 00:00:00'

Your condition would not return rows where the end_time is e.g. 2023-01-01 23:59:59.999