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.

Answer 1

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.