Question 90331 in PostgreSQL Question posted in
The official documentation can be found here.

Prepared Statement Does Not Exists, PostgreSQL

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);
Tags:

2

Answers


  1. 0

    1. As pointed out in the comments, to use a procedure, you need to CALL your_procedure();.

    2. 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.

    3. 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.

    4. 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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search