11

I'm trying to store a simple SELECT query with the new CREATE PROCEDURE method in PostgreSQL 11. My idea is to store the queries in the DB, because I can have a much simple code in my API server and maybe I don't need to develop a query builder if I can use if/else in an sql function with enforced type safety. I have this minimal example:

First I tried this plpgsql function:

CREATE OR REPLACE PROCEDURE test_proc() AS $$
    BEGIN
        SELECT * FROM my_db
        LIMIT 1;
    END;
$$ LANGUAGE plpgsql;

CALL test_proc();

However throws 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 test_proc() line 3 at SQL statement SQL state: 42601

If I trying to use RETURN QUERY:

CREATE OR REPLACE PROCEDURE test_proc() AS $$
    BEGIN
        RETURN QUERY;
        SELECT * FROM my_db
        LIMIT 1;
    END;
$$ LANGUAGE plpgsql;

I'm getting this error:

ERROR: cannot use RETURN QUERY in a non-SETOF function LINE 17: RETURN QUERY; ^ SQL state: 42804 Character: 310

I'm also getting error when I try to use RETURNS void AS $$ or RETURNS table(...) AS $$. Seems like RETURNS not supported in CREATE PROCEDURE? So, is it possible to return a table with the new stored procedure method? Or if it's not, maybe JSON?

Improve this question
2
  • 2
    If you need return something, use function, not procedure Commented Aug 28, 2018 at 19:43
  • 2
    So probably CREATE PROCEDURE is recommended for INSERT (because of the transaction support) and I need to use functions for queries if I want to return table? It's fine for me. Commented Aug 28, 2018 at 19:44

3 Answers 3

Reset to default
13

Procedures in PostgreSQL (Oracle, DB2) are not same like procedures in MS-SQL. It has different target, and you cannot use it. Usually, the best what you can do, forgot all what you know from MSSQL. The procedural part is really different.

Only functions can returns some data - so you need to use functions. Functions can returns scalar value, composite value or array value, or table. You want function that returns table.

CREATE OR REPLACE FUNCTION fx()
RETURNS SETOF mytab AS $$
BEGIN
  RETURN QUERY SELECT * FROM mytab;
END
$$ LANGUAGE plpgsql;

SELECT * FROM fx();

For record:

You can use SQL function, that can have better (or worse) performance (depends on context). These functions are sometimes named as parametrized views.

CREATE OR REPLACE FUNCTION fx()
RETURNS SETOF mytab AS $$
  SELECT * FROM mytab;
$$ LANGUAGE sql;

Attention: this technique is antipattern!!! Don't do it. It is really not good idea. The functions should not to wrap queries. If you want to hide some complexity of queries, then use a views. Don't use a functions. Functions are effective barier for query optimizer, and when you use this antipattern, then optimizer cannot to well optimize any non trivial queries that use in this form evaluated subqueries.

Use it - if you want very very slow applications - or if your data model or queries are primitive. In other cases, don't do it.

Don't afraid of SQL - it is great language designed for manual usage. It is good to place all data access to one module (model), to don't access database everywhere in your code, but it is bad too hide SQL in your code.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

You forgot to mention SQL functions. Those aren't a barrier for the optimizer and are a good way to implement "views with parameters"
@a_horse_with_no_name - it is working, only when a inlining is available, and it is not possible always. Now, after my experience, I am thinking (and it is my opinion), so query wrapping is just antipattern (and although there are some exceptional) is better to doesn't use it. My reply is not exact (I know), but it is safe. It is sad :-(
3

First of all Procedure was introduced in PostgreSQL 11, If you are using below 11th version, you cannot use Procedures. Instead to Procedure you can use functions.

Syntax to create function

    CREATE or replace function function_name(_parameter varchar) 
    returns table(col1 varchar, col2 varchar, col3 varchar) 
          language 'plpgsql' 
          as $BODY$ 
          BEGIN
                  return query select a.col1, a.col2, b.col3 from table a
                  join table2 as b on a.col1 = b.col1;
        END;
        $BODY$;

you can call a function same a like table 

select * From function_name('sample data');

syntax to create Procedure.

CREATE OR REPLACE PROCEDURE procedure_name(_parameter varcar,INOUT result refcursor)
LANGUAGE 'plpgsql'
AS $BODY$         
BEGIN
open result for SELECT ,  * from sampletable where a = _parameter; 
END;
$BODY$;

you can execute a Procedure using call keyword, within a transaction 

BEGIN;
CALL public.procedure_name( 'sample data',  'test');
fetch all in "test";
COMMIT;
Improve this answer

10 Comments

"Function will not support dynamic queries" - wrong
"You can not use DML like insert/update/delete queries in functions" - wrong
"Return type of function should match structure of table in function." - wrong. You can define any output structure you like. So that sentence should be "should match structure of query in function"
@a_horse_with_no_name, have you tried dynamic queries, and DML queries in Postgresql functions. if you tried share to us. when i tried i failed to do that.
Then you did something wrong, because that most definitely works.
|
0

The postgreSql 11. we have to create a stored procedure

there is the solution : Create procedure to execute query in PostgreSQL

Improve this answer

2 Comments

A link to a solution is welcome, but please ensure your answer is useful without it: add context around the link so your fellow users will have some idea what it is and why it’s there, then quote the most relevant part of the page you're linking to in case the target page is unavailable. Answers that are little more than a link may be deleted.
the solution is ensure by myself. its working and tested by myself.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.