PostgreSQL stored procedure to get an employee by input ID from a joined table

Question

I am trying to write a stored procedure to get an employee by input ID. In SQL Server, it looks like this:

CREATE PROCEDURE [dbo].[GetbyID]
    (@ID NVARCHAR(50))
AS
BEGIN
    SELECT * 
    FROM dbo.Employee AS M
    LEFT OUTER JOIN dbo.Position AS F1 ON M.PositionID = F1.PositionID
    LEFT OUTER JOIN dbo.Department AS F2 ON F1.DepartmentID = F2.DepartmentID
    WHERE M.ID = @ID
END

I re-write it in PostgreSQL like this:

DROP PROCEDURE IF EXISTS GetbyID(p_ID TEXT);

CREATE OR REPLACE PROCEDURE GetbyID(p_ID TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT * FROM "Employee" AS M
    LEFT OUTER JOIN "Position" AS F1 ON M."PositionID" = F1."PositionID_P"
    LEFT OUTER JOIN "Department" AS F2 ON F1."DepartmentID_P" = F2."DepartmentID"
    WHERE M."ID" = p_ID;
END;
$$;

It says "Query returned successfully", however, when I try to test by CALL, for example:

CALL GetbyID('E12345');

I get this error:

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function getbyid(text) line 3 at SQL statement
SQL state: 42601

How can I fix this issue? Thank you.

Answer 1

First, read documentation for PL/pgSQL. It is nothink against you, just if you have experience with MSSQL - your example looks like T-SQL, you should to forget all about procedures. PostgreSQL is similar to Oracle, and writing stored procedures in Oracle or PostgreSQL is very different than for MSSQL.

The procedures in PostgreSQL cannot to return resultset. If you can, you can use OUT variables, but this is not case for procedure. Procedures in Postgres should be used, when you want to control transactions, when you want (or need) to use statements COMMIT or ROLLBACK inside code. Elsewhere you should to use functions.

create table foo(id int primary key, a int, b int);
insert into foo values(1, 10, 20);
insert into foo values(2, 30, 40);

create table boo(foo_id int, z int);
insert into boo values(1, 100);
insert into boo values(2, 1000);

CREATE OR REPLACE PROCEDURE public.foo_boo(OUT a integer, OUT b integer, OUT z integer, IN f integer)
 LANGUAGE plpgsql
AS $procedure$
begin
  select foo.a, foo.b, boo.z into a, b, z 
    from foo join boo on foo.id = boo.foo_id
    where foo_id = f;
  return;
end;
$procedure$

(2024-02-22 12:20:16) postgres=# call foo_boo(null, null, null, 1);
┌────┬────┬─────┐
│ a  │ b  │  z  │
╞════╪════╪═════╡
│ 10 │ 20 │ 100 │
└────┴────┴─────┘
(1 row)

It is working, but if you want to hide dependency between tables and if you want to simplify usage, just use view:

create view foo_boo_v as 
  select foo.a, foo.b, boo.z, boo.foo_id
    from foo join boo on foo.id = boo.foo_id;
 
CREATE VIEW
(select * from foo_boo_v where foo_id = 1;
┌────┬────┬─────┬────────┐
│ a  │ b  │  z  │ foo_id │
╞════╪════╪═════╪════════╡
│ 10 │ 20 │ 100 │      1 │
└────┴────┴─────┴────────┘
(1 row)

Or you can use function. For these one statement functions, the best functions are functions written in SQL language

create or replace function foo_boo_f(out a int, out b int, out z int, f int)
returns setof record as $$ 
  select foo.a, foo.b, boo.z 
    from foo join boo on foo.id = boo.foo_id
    where foo_id = f;
$$ language sql stable;

Don't forgot to set flag stable. Without it, the query will not be inlined, and not inlined SQL functions are not fast. You can verify inlining:

(2024-02-22 12:28:37) postgres=# explain analyze select * from foo_boo_f(1);
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                     QUERY PLAN                                                      │
╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Nested Loop  (cost=0.15..46.53 rows=11 width=12) (actual time=0.054..0.061 rows=1 loops=1)                          │
│   ->  Index Scan using foo_pkey on foo  (cost=0.15..8.17 rows=1 width=12) (actual time=0.026..0.029 rows=1 loops=1) │
│         Index Cond: (id = 1)                                                                                        │
│   ->  Seq Scan on boo  (cost=0.00..38.25 rows=11 width=8) (actual time=0.021..0.023 rows=1 loops=1)                 │
│         Filter: (foo_id = 1)                                                                                        │
│         Rows Removed by Filter: 1                                                                                   │
│ Planning Time: 0.511 ms                                                                                             │
│ Execution Time: 0.130 ms                                                                                            │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(8 rows)

The PL/pgSQL can be used too:

create or replace function foo_boo_f(out a int, out b int, out z int, f int) returns setof record as $$
begin
  return query select foo.a, foo.b, boo.z 
    from foo join boo on foo.id = boo.foo_id
    where foo_id = f;
end;
$$ language plpgsql stable;
CREATE FUNCTION
(2024-02-22 12:32:39) postgres=# explain analyze select * from foo_boo_f(1);
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                 QUERY PLAN                                                  │
╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Function Scan on foo_boo_f  (cost=0.25..10.25 rows=1000 width=12) (actual time=0.833..0.834 rows=1 loops=1) │
│ Planning Time: 0.119 ms                                                                                     │
│ Execution Time: 0.891 ms                                                                                    │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(3 rows)

PL/pgSQL function cannot be inlined ever, but execution of plpgsql function should be faster than execution not inlined SQL function (from more reasons).

Again - very important is correct setting of flag stable. Without it, the function is marked as volatile and some possible optimizations are blocked. Read about it in documentation - the topic about keywords volatile, stable or immutable is pretty important for performance.

Procedures or functions can return cursors. Internally it is similar what does T-SQL.

CREATE OR REPLACE PROCEDURE public.foo_boo_c(IN f integer, OUT c refcursor)
 LANGUAGE plpgsql
AS $procedure$
begin
  open c for select foo.a, foo.b, boo.z              
    from foo join boo on foo.id = boo.foo_id
    where foo_id = f;
  return;
end;
$procedure$
;
CREATE PROCEDURE
(2024-02-23 07:09:52) postgres=# begin;
BEGIN
(2024-02-23 07:09:59) postgres=# call foo_boo_c(1, NULL);
┌────────────────────┐
│         c          │
╞════════════════════╡
│ <unnamed portal 1> │
└────────────────────┘
(1 row)

(2024-02-23 07:10:14) postgres=# fetch all from "<unnamed portal 1>";
┌────┬────┬─────┐
│ a  │ b  │  z  │
╞════╪════╪═════╡
│ 10 │ 20 │ 100 │
└────┴────┴─────┘
(1 row)

(2024-02-23 07:10:39) postgres=# close "<unnamed portal 1>";
CLOSE CURSOR
(2024-02-23 07:11:00) postgres=# call foo_boo_c(2, NULL);
┌────────────────────┐
│         c          │
╞════════════════════╡
│ <unnamed portal 2> │
└────────────────────┘
(1 row)

(2024-02-23 07:11:06) postgres=# fetch all from "<unnamed portal 2>";
┌────┬────┬──────┐
│ a  │ b  │  z   │
╞════╪════╪══════╡
│ 30 │ 40 │ 1000 │
└────┴────┴──────┘
(1 row)

(2024-02-23 07:11:32) postgres=# close "<unnamed portal 2>";
CLOSE CURSOR
(2024-02-23 07:11:37) postgres=# commit;
COMMIT

If you don't call this routine recursively, you can pass cursor name as argument, but you should to ensure so this name will not be used in this time:

CREATE OR REPLACE PROCEDURE public.foo_boo_c2(IN f integer, c refcursor)
 LANGUAGE plpgsql
AS $procedure$
begin
  open c for select foo.a, foo.b, boo.z
    from foo join boo on foo.id = boo.foo_id
    where foo_id = f;
  return;
end;
$procedure$
;
CREATE PROCEDURE
(2024-02-23 07:18:52) postgres=# begin;                                                                  
BEGIN
(2024-02-23 07:18:56) postgres=# call foo_boo_c2(2, 'mycursor');
CALL
(2024-02-23 07:19:13) postgres=# fetch all from mycursor;
┌────┬────┬──────┐
│ a  │ b  │  z   │
╞════╪════╪══════╡
│ 30 │ 40 │ 1000 │
└────┴────┴──────┘
(1 row)

(2024-02-23 07:19:28) postgres=# close mycursor;
CLOSE CURSOR
(2024-02-23 07:19:34) postgres=# commit;
COMMIT

Answer 2

I would use a function rather then a stored procedure, functions can return a result set, which is what you seem to be trying to achieve with your SELECT statement.

DROP FUNCTION IF EXISTS GetbyID(TEXT);

CREATE OR REPLACE FUNCTION GetbyID(p_ID TEXT)
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY SELECT M.*, F1.*, F2.* FROM "Employee" AS M
    LEFT OUTER JOIN "Position" AS F1 ON M."PositionID" = F1."PositionID_P"
    LEFT OUTER JOIN "Department" AS F2 ON F1."DepartmentID_P" = F2."DepartmentID"
    WHERE M."ID" = p_ID;
END;
$$;

To call this function and retrieve its result, you would use the following:

SELECT * FROM GetbyID('E12345') AS t( /* column list */ );

When using AS t(/* column list */);, you need to specify the structure of the returned table by listing the columns and their data types that match the output of your function. For example, if your function returns two columns, employee_name TEXT and department_name TEXT, you would write:

SELECT * FROM GetbyID('E12345') AS t(employee_name TEXT, department_name TEXT);