ERROR: function add(integer, integer) does not exist No function matches the given name and argument types. You might need to add explicit type casts

Question

I have tried various ways to solve this issue: i tried it with the code SELECT add(1, 2); but still getting the same result. I have looked at other stack overflow asnwers but they are not working either.

-- Test the add function
SELECT add(1::int, 2::int);
ERROR:  function add(integer, integer) does not exist
LINE 1: SELECT add(1::int, 2::int);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

What changes should i make to the my_extension--regress.sql to fix this as the add function is working just fine in the psql db spartacus:

spartacus=# SELECT add(3,5)                                                                                                                        ;
 add
-----
   8
(1 row)

The code of the various files are given below:

CODE:

my_extension--1.0.sql:

-- Create necessary objects for version 1.0
CREATE TABLE my_table (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

-- Add 2 numbers using add function
CREATE FUNCTION add(a integer, b integer) RETURNS integer
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    RETURN a + b;

my_extension--regress.sql:

-- regression test script for my_extension
-- Create the my_table table
CREATE TABLE my_table (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

-- Delete existing rows from my_table
DELETE FROM my_table;

-- Verify the data in the my_table table
SELECT * FROM my_table;

-- Test the add function
SELECT add(1::int, 2::int);

After running the make insatll funtion getting error:

echo "+++ regress install-check in  +++" && /Users/spartacus/.pgenv/pgsql-15.0/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/Users/spartacus/.pgenv/pgsql-15.0/bin'    --dbname=contrib_regression my_extension--regress
+++ regress install-check in  +++
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
SET
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test my_extension--regress        ... FAILED       17 ms

======================
 1 of 1 tests failed.
======================

The differences that caused some tests to fail can be viewed in the
file "/Users/spartacus/Desktop/GSoC/CODE/my_extension/regression.diffs".  A copy of the test summary that you see
above is saved in the file "/Users/spartacus/Desktop/GSoC/CODE/my_extension/regression.out".

make: *** [installcheck] Error 1

my_extension--regress.out:

-- regression test script for my_extension
-- Create the my_table table
CREATE TABLE my_table (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);
-- Delete existing rows from my_table
DELETE FROM my_table;
-- Verify the data in the my_table table
SELECT * FROM my_table;
 id | name 
----+------
(0 rows)

-- Test the add function
SELECT add(1::int, 2::int);
ERROR:  function add(integer, integer) does not exist
LINE 1: SELECT add(1::int, 2::int);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Answer 1

Regression file is wrong:

my_extension--regress.out:

-- regression test script for my_extension
CREATE EXTENSION my_extension;

-- Verify the data in the my_table table
SELECT * FROM my_table;

-- Test the add function
SELECT add(1, 2);

As pointed out be Laurenz Albe: Didn't run CREATE EXTENSION in your regression test script, so the extension does not exist. Because of which the add function doesn't exist.