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.
2
Answers
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.To call this function and retrieve its result, you would use the following:
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: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 statementsCOMMIT
orROLLBACK
inside code. Elsewhere you should to use functions.It is working, but if you want to hide dependency between tables and if you want to simplify usage, just use view:
Or you can use function. For these one statement functions, the best functions are functions written in SQL language
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:The PL/pgSQL can be used too:
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 asvolatile
and some possible optimizations are blocked. Read about it in documentation – the topic about keywordsvolatile
,stable
orimmutable
is pretty important for performance.