sql - Postgres 函数创建但不执行

Question

我一直在尝试很多事情来让它发挥作用。据我所知，我已经按照文档进行操作，但这是行不通的。

任何有 Postgres 经验的人都可以推荐一个修复程序吗？我将永远感激不已。

我使用的是版本 1.18.1(2014 年 6 月 9 日，修订版:REL-1_18_1)

我的创建代码:

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;

我的执行/调用代码:

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:  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

我有过类似的情况 - 参数列表很宽的函数。使用所谓的命名参数，您无需遵守参数的顺序。代码更长，但(我希望)更易读且更健壮。

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$

这个函数可以用命名参数来调用:

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

注意:当我使用错误的类型时 - Postgres 报告消息:

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 existLINE 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);

你可以写一个构造函数:

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$

创建这个系统需要一些工作，它只适用于长期存在的系统。另一方面，它降低了 future 维护工作的成本。

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$

现在，更多的符号(符号的组合)是可能的:

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

(1 row)

构造函数有助于定位错误:

postgres=# SELECT fx(_person_type('Pavel','Stehule'),          _address_type('Skalice 12','Benesov', 25601));ERROR:  function _address_type(unknown, unknown, integer) does not existLINE 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.