Stored procedures and out parameters

From: Shay Rojansky <roji(at)roji(dot)org>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Stored procedures and out parameters
Date: 2018-07-23 06:23:38
Message-ID: CADT4RqAo=+m7zBuGsLzARf6ui90Humf7CJZR+zADXM+2oGvKUA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi hackers, I've encountered some odd behavior with the new stored
procedure feature, when using INOUT parameters, running PostgreSQL 11-beta2.

With the following procedure:

CREATE OR REPLACE PROCEDURE my_proc(INOUT results text)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
select 'test' into results;
END;
$BODY$;

executing CALL my_proc('whatever') yields a resultset with a "results"
column and a single row, containing "test". This is expected and is also
how functions work.

However, connecting via Npgsql, which uses the extended protocol, I see
something quite different. As a response to a Describe PostgreSQL message,
I get back a NoData response rather than a RowDescription message, In other
words, it would seem that the behavior of stored procedures differs between
the simple and extended protocols, when INOUT parameters are involved. Let
me know if you need any more info.

It may be worth adding some more documentation in
https://www.postgresql.org/docs/11/static/sql-createprocedure.html which
doesn't mention OUT/INOUT parameters at all (for instance, the fact that
OUT parameters aren't allowed, and an explanation why INOUT parameter are
allowed etc.).

Thanks for your help!

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrey Lepikhov 2018-07-23 06:25:42 Re: [WIP] [B-Tree] Retail IndexTuple deletion
Previous Message amul sul 2018-07-23 06:14:24 Re: code of partition split