Postgres函数创建但不执行 [英] Postgres function creates but does not execute
问题描述
我一直在尝试很多事情来使它起作用.据我所知，我已经按照文档进行了操作，但这是行不通的.
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.
任何有Postgres经验的人都可以推荐解决方案吗?我将永远感激不已.
Can any of you with experience in Postgres recommend a fix? I would be eternally grateful.
我正在使用版本1.18.1(2014年6月9日，修订版:REL-1_18_1)
I'm using Version 1.18.1 (jun 9 2014, rev: 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
推荐答案
我也遇到类似的情况-具有宽参数列表的函数.使用所谓的命名参数，您无需遵守参数的顺序.代码更长，但是(我希望)代码更具可读性和健壮性.
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');
注意:当我使用错误的类型时-Postgres报告消息:
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支持自定义类型.您可以使用它:
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.
这篇关于Postgres函数创建但不执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
