Re: [PL/pgSQL] function call

On Mon, 31 Oct 2011 09:41:40 -0500
Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

>On Mon, Oct 31, 2011 at 8:31 AM, Tarlika Elisabeth Schmitz
><postgresql6(at)numerixtechnology(dot)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