How to pass int2 parameter to a stored function?

Question

I have the following function stored in a Postgres 15 database.
I use dbeaver to connect to the database.

CREATE OR REPLACE FUNCTION schema_name.function_name(
    in input_user_id int2
)
RETURNS TABLE(
    column_1 smallint,
    column_2 character varying,
    column_3 character varying,
    column_4 timestamp with time zone,
    column_5 timestamp with time zone,
    column_6 character varying,
    column_7 int2,
    column_8 int2
)
LANGUAGE plpgsql
AS $function$
begin
    return query
    select * 
    from schema_name.table_name
    where table_name.column_5 = input_user_id;
end;
$function$
;

If I run the query part directly on dbeaver, I get the expected results:

select * from schema_name.table_name where column_5 = 1;

If I run the function I get errors:

select * from schema_name.function_name(1);

SQL Error [42883]: ERROR: function schema_name.function_name(integer) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 15

All of these get me a different error:

select * from schema_name.function_name(int2 '1');
select * from schema_name.function_name(1::int2);
select * from schema_name.function_name(1::smallint);

SQL Error [42804]: ERROR: structure of query does not match function result type
Detail: Returned type text does not match expected type smallint in column 7.
Where: SQL statement "select * 
  from schema_name.table_name
  where table_name.column_5 = input_user_id"
PL/pgSQL function schema_name.function_name(smallint) line 3 at RETURN QUERY

I also typecasted to integer and got another error.

What am I doing wrong?

Note, I added the full list of columns since the error mentions the specific columns, but I didn't edited the error messages.

The second error is clear: your returns table does not match the structure of schema_name.table_name. You might be better off setting this up as returns setof schema_name.table_name: demo. — Zegarek
– Zegarek, Commented Mar 15, 2024 at 1:01
You are right, i missed that column 6 was text instead of character varying. Silly mistake that has taken me a lo of time. Thanks — PatomaS
– PatomaS, Commented Mar 15, 2024 at 1:12
Since there was no real problem I'm inclined to delete the question to reduce clutter on the system, but that would remove the point to you @Zegarek, So I won't do it unles you agree. Thanks again — PatomaS
– PatomaS, Commented Mar 15, 2024 at 1:14
I vote against. Now that Erwin’s answer covers this and much more, I’d accept that. Added benefit of setof is that the function will always reflect changes to the table, like added/renamed/dropped/altered columns, without you having to maintain it by manually cascading the change: demo — Zegarek
– Zegarek, Commented Mar 15, 2024 at 1:16

Erwin Brandstetter · Accepted Answer · 2024-03-15 01:23:49Z

There is no implicit cast from int4 → int2 (for good reasons). So you must pass an actual int2 (int2 '1') or an untyped string literal ('1'). Detailed assessment and more options:

ERROR: function ... does not exist and HINT: No function matches the given name and argument types

More problems:

smallserial is not an actual data type. You must use int2 (a.k.a. smallint) in RETURNS TABLE. See:

Data type "smallserial" in Postgres not highlighted in Visual Studio Code

Don't use SELECT * when the function returns a list of columns. Use a matching SELECT list.

Use a table alias and table-qualify all column names that might conflict with OUT parameters. (All columns listed in RETURNS TABLE are OUT parameters effectively.)

This would work:

CREATE OR REPLACE FUNCTION schema_name.function_name(input_user_id int2)
  RETURNS TABLE (
         column_1 int2  -- makes no sense: smallserial
       , column_2 varchar
       , column_3 timestamptz
       , column_4 varchar
       , column_5 int2
   )
LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.column_1, t.column_2, t.column_3, t.column_4, t.column_5 
   FROM   schema_name.table_name t
   WHERE  t.column_5 = input_user_id;
END
$func$;

Call:

SELECT * FROM schema_name.function_name('1');

If you truly want to return the whole row as defined by schema_name.table_name, use RETURNS SETOF schema_name.table_name instead. Now it's ok to use SELECT * in the function body. Also, fewer naming collisions:

CREATE OR REPLACE FUNCTION schema_name.function_name(input_user_id int2)
  RETURNS SETOF schema_name.table_name
  LANGUAGE sql AS
$func$
SELECT *
FROM   schema_name.table_name
WHERE  column_5 = input_user_id;
$func$;

Also demonstrating a simpler SQL function, which does the simple job.

Collectives™ on Stack Overflow

How to pass int2 parameter to a stored function?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related