Postgres函数创建但不执行

本文介绍了Postgres函数创建但不执行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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.