Postgres function creates but does not execute

Question

I've been trying many things to get this to work. I've followed the documentation, as far as I know, and this is just not working.

Can any of you with experience in Postgres recommend a fix? I would be eternally grateful.

I'm using Version 1.18.1 (jun 9 2014, rev: REL-1_18_1)

My create code:

set search_path = PsychoProductions;
create or replace function fcn_insert_person(
        -- person table
        prm_role_id int, 
        prm_first_name text, 
        prm_last_name text, 
        prm_organization text, 
        prm_website text, 
        prm_default_billing_method_id text, 
        prm_active boolean, 
        -- address table
        prm_address_type_id int, 
        prm_address text, 
        prm_city text, 
        prm_state text, 
        prm_zip_code text, 
        -- email table
        prm_email_address text, 
        prm_email_type_id int, 
        -- phone table
        prm_phone_number text, 
        prm_phone_type_id int
)
returns void as
$$
set search_patch = PsychoProductions;

insert into PsychoProductions.person (
        role_id,
        first_name,
        last_name,
        organization,
        website,
        default_billing_method_id,
        active
        )
values (
        prm_role_id,
        prm_first_name,
        prm_last_name,
        prm_organization,
        prm_website,
        prm_default_Billing_Method_ID,
        prm_active
        );

insert into PsychoProductions.address (
        person_id,
        address_type_id,
        address,
        city,
        state,
        zip_code
        )
values (
        ( select currval('person_id_seq') ),
        prm_address_type_id,
        prm_address,
        prm_city,
        prm_state,
        prm_zip_code
        );

insert into email (
        person_id,
        email_address,
        email_type_id
        )
values (
        ( select currval('person_id_seq') ),
        prm_email_address,
        prm_email_type_id
        );

insert into phone (
        person_id,
        phone_number,
        phone_type_id
        )
values (
        ( select currval('person_id_seq') ),
        prm_phone_number,
        prm_phone_type_id
        );

-- end;
$$
language sql;

My execute/call code:

set search_path = PsychoProductions;
select fcn_insert_person(
-- NOTE: DO NOT REMOVE QUOTATION MARKS
        -- person table
        3,                      -- customer
        'firstname', 
        'lastname', 
        'organization', 
        'website', 
        2,                      -- net 30
        True,                   -- active
        -- address table
        1,                      -- unique
        'address', 
        'city', 
        'state', 
        'zip', 
        -- email table
        'email', 
        1,                      -- business email 
        -- phone table
        'phone', 
        1                       -- mobile
  );

Error:

ERROR:  function fcn_insert_person(integer, unknown, unknown, unknown, unknown, integer, boolean, integer, unknown, unknown, unknown, unknown, unknown, integer, unknown, integer) does not exist
LINE 2: select fcn_insert_person(
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function fcn_insert_person(integer, unknown, unknown, unknown, unknown, integer, boolean, integer, unknown, unknown, unknown, unknown, unknown, integer, unknown, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 45

Answer 1

I had a similar situation - function with wide parameter list. With so called named parameters, you don't need to respect a order of parameters. Code is longer, but (I hope) more readable and more robust.

CREATE TABLE tab(name text, surname text, address text, city text, zip text);

CREATE OR REPLACE FUNCTION public.fx(name text, surname text,
                                     address text, city text, zip text)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
  INSERT INTO tab(name, surname, address, city, zip)
    VALUES(fx.name, fx.surname, fx.address, fx.city, fx.zip);
  -- ... some other logic
END;
$function$

This function can be called with named parameters notation:

SELECT fx(name := 'Pavel', surname := 'Stehule',
          address := 'Skalice 12', city := 'Benesov', zip := '12');

Attention: When I use wrong type - Postgres reports message:

postgres=#   SELECT fx(name := 'Pavel', surname := 'Stehule',
              address := 'Skalice 12', city := 'Benesov', zip := 12);
ERROR:  function fx(name := unknown, surname := unknown, address := unknown, city := unknown, zip := integer) does not exist
LINE 1: SELECT fx(name := 'Pavel', surname := 'Stehule',
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

The message is valid, but it is not clean. It is a cost of function overloading support. There is other trick, how to divide long parameter list, and how to find these issues more comfortably.

Postgres support custom types. You can use it:

CREATE TYPE person_type AS (name text, surname text);
CREATE TYPE address_type AS (address text, city text, zip text);

you can write a constructor functions:

CREATE OR REPLACE FUNCTION public._person_type(name text, surname text)
RETURNS person_type
LANGUAGE plpgsql
AS $function$
DECLARE r person_type;
BEGIN
  r.name = name;
  r.surname = surname;
  RETURN r;
END;
$function$

CREATE OR REPLACE FUNCTION public._address_type(address text, city text, zip text)
RETURNS address_type
LANGUAGE plpgsql
AS $function$ DECLARE r address_type;
BEGIN
  r.address = address;
  r.city = city;
  r.zip = zip;
  RETURN r;
END;
$function$

Creating this system needs some work and it is practical for long live systems only. On second hand, it reduce a cost for future maintaining work.

CREATE OR REPLACE FUNCTION public.fx(p person_type, a address_type)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
  INSERT INTO tab(name, surname, address, city, zip)
    VALUES(p.name, p.surname, a.address, a.city, a.zip);
   -- ... some other logic
END;
$function$

Now, more notations (combination of notations) are possible:

postgres=# SELECT fx(_person_type('Pavel','Stehule'),
postgres(#           _address_type('Skalice 12','Benesov', '25601'));
 fx 
----

(1 row)

Constructors helps with error localization:

postgres=# SELECT fx(_person_type('Pavel','Stehule'),
          _address_type('Skalice 12','Benesov', 25601));
ERROR:  function _address_type(unknown, unknown, integer) does not exist
LINE 2:           _address_type('Skalice 12','Benesov', 25601));
                  ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Answer 2

The parameter types in your function call don't match the parameter types in the function definition. In particular, prm_default_billing_method_id is supposed to be text, but you passed it an integer 2 instead. You could use '2', 2::text, or cast(2 as text).

In addition, within your function, you have a typo around line 26: set search_patch instead of set search_path.