Re: Debugging --- ERROR: function does not exist

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Mark <nvpostgres(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Debugging --- ERROR: function does not exist
Date: 2026-10-25 20:55:32
Message-ID: 9bc98c9a-f5f0-424b-9eac-4cfe18820fa1@aklaver.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 10/25/26 1:19 PM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
>> So is the problem down to the OP defining a function not a procedure and
>> therefore not being able to use CALL to get at the OUT parameters?
>
> Yeah. I was thinking that plpgsql's version of CALL was more
> forgiving of that, but I was wrong: it too wants the target to be
> declared as a procedure.

Alright to get it straight in my head and maybe of use to OP:

CREATE OR REPLACE PROCEDURE public.call_test(
IN a character varying, IN b character varying,
OUT x integer, OUT y integer)
LANGUAGE plpgsql
AS $procedure$
BEGIN
x = a::int + b::int;
y = a::int - b::int;
END;
$procedure$
;

CREATE OR REPLACE FUNCTION public.select_test(
a character varying, b character varying,
OUT x integer, OUT y integer)
RETURNS record
LANGUAGE plpgsql
AS $function$
BEGIN
x = a::int + b::int;
y = a::int - b::int;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.combined_test(
a character varying, b character varying)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
x int;
y int;
r record;
BEGIN
SELECT INTO r * FROM select_test(a, b);
RAISE NOTICE '%', r;
--CALL call_test(a, b, x, y);
--RAISE NOTICE '%, %', x, y;

END;
$function$
;

When combined_test is run as above using the SELECT then:

select combined_test('1', '2');
NOTICE: (3,-1)
combined_test
---------------

When run as CALL:

CREATE OR REPLACE FUNCTION public.combined_test(
a character varying, b character varying)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
x int;
y int;
r record;
BEGIN
--SELECT INTO r * FROM select_test(a, b);
--RAISE NOTICE '%', r;
CALL call_test(a, b, x, y);
RAISE NOTICE '%, %', x, y;

END;
$function$
;

select combined_test('1', '2');
NOTICE: 3, -1
combined_test
---------------

>
> regards, tom lane

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Durgamahesh Manne 2026-10-26 04:55:06 Re: Regarding huge logical slot lag between source and target
Previous Message David G. Johnston 2026-10-25 20:33:45 Re: Debugging --- ERROR: function does not exist