Re: [PL/pgSQL] function call - Mailing list pgsql-general

From Tarlika Elisabeth Schmitz
Subject Re: [PL/pgSQL] function call
Date
Msg-id 20111031181808.4d6b4a6b@dick.coachhouse
Whole thread Raw
In response to Re: [PL/pgSQL] function call  (Merlin Moncure <mmoncure@gmail.com>)
List pgsql-general
On Mon, 31 Oct 2011 09:41:40 -0500
Merlin Moncure <mmoncure@gmail.com> wrote:

>On Mon, Oct 31, 2011 at 8:31 AM, Tarlika Elisabeth Schmitz
><postgresql6@numerixtechnology.de> wrote:
>> I have created a function log_insert(), which is simply a shorthand
>> for an INSERT table and which I want to call from various trigger
>> functions.
>>
>> CREATE OR REPLACE FUNCTION log_insert(vseverity text, vtrigger text,
>> vtriggertable text, vtriggerid text, vmessage text)
>>[...]
>>
>> SELECT log_insert('I', TG_NAME, TG_TABLE_NAME, NEW.id, 'some msg')
>> => [...] No function matches the given name and argument
>> types. You might need to add explicit type casts."
>>
>>
>
>[...] in pl/pgsql, you always call functions with
>PERFORM or SELECT depending if you want to process the result.
>
>also, FWIW, I don't like a simple wrapper for insert statement like
>that -- the syntax brevity is outweighed by the loss of SQL features
>such as being able to pass DEFAULT for columns.
>
>merlin
>

Thank you for your reply.

I don't feel entirely comfortable about phrasing an INSERT as "SELECT
log_insert()". As for losing SQL features - no loss in
this particular scenario.

I simply thought my PL/pgSQL code would look a little less cluttered
with a one-line call than with a 3-line INSERT:

INSERT INTO log
(severity, trigger, triggertable, triggerid, message) VALUES
('I', TG_NAME, TG_TABLE_NAME, NEW.id, 'some message');


I had two errors:
1) I needed an explicit type cast for the integer NEW.id to ::text
2) the function was declared as RETURNS boolean but did not return a
value.

--

Best Regards,
Tarlika Elisabeth Schmitz

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Query planner always has exactly half of the rows in the table as "plan rows" with new GIST operator class
Next
From: "Gauthier, Dave"
Date:
Subject: does reindex need exclusive table access?