Thread: [PL/pgSQL] function call

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) RETURNS boolean AS
$BODY$
BEGIN
  INSERT INTO log
  (severity, trigger,triggertable, triggerid, message)
  VALUES
  (vseverity, vtrigger,vtriggertable, vtriggerid, vmessage);
END
$BODY$
LANGUAGE plpgsql VOLATILE;


I tried:
log_insert('I', TG_NAME, TG_TABLE_NAME, NEW.id, 'some msg');
=> I get a syntax error on CREATE TRIGGER.

SELECT log_insert(...)
=> passes the syntax check but throws an error when run:
"function log_insert(unknown, unknown, unknown, integer, unknown) does
not exist Hint: No function matches the given name and argument types.
You might need to add explicit type casts."


Any help would be greatly appreciated.
--

Best Regards,
Tarlika Elisabeth Schmitz

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) RETURNS boolean AS
> $BODY$
> BEGIN
>  INSERT INTO log
>  (severity, trigger,triggertable, triggerid, message)
>  VALUES
>  (vseverity, vtrigger,vtriggertable, vtriggerid, vmessage);
> END
> $BODY$
> LANGUAGE plpgsql VOLATILE;
>
>
> I tried:
> log_insert('I', TG_NAME, TG_TABLE_NAME, NEW.id, 'some msg');
> => I get a syntax error on CREATE TRIGGER.
>
> SELECT log_insert(...)
> => passes the syntax check but throws an error when run:
> "function log_insert(unknown, unknown, unknown, integer, unknown) does
> not exist Hint: No function matches the given name and argument types.
> You might need to add explicit type casts."
>
>
> Any help would be greatly appreciated.

There is some context you are not passing here -- the log_insert
function is being inside a trigger function which is where your error
always is.  However, 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

2011/10/31 Merlin Moncure <mmoncure@gmail.com>:
> 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) RETURNS boolean AS
>> $BODY$
>> BEGIN
>>  INSERT INTO log
>>  (severity, trigger,triggertable, triggerid, message)
>>  VALUES
>>  (vseverity, vtrigger,vtriggertable, vtriggerid, vmessage);
>> END
>> $BODY$
>> LANGUAGE plpgsql VOLATILE;
>>
>>
>> I tried:
>> log_insert('I', TG_NAME, TG_TABLE_NAME, NEW.id, 'some msg');
>> => I get a syntax error on CREATE TRIGGER.
>>
>> SELECT log_insert(...)
>> => passes the syntax check but throws an error when run:
>> "function log_insert(unknown, unknown, unknown, integer, unknown) does
>> not exist Hint: No function matches the given name and argument types.
>> You might need to add explicit type casts."
>>
>>
>> Any help would be greatly appreciated.
>
> There is some context you are not passing here -- the log_insert
> function is being inside a trigger function which is where your error
> always is.  However, 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.
>

you can use a PL default parameters now. And when there are lot of
parameters a named notation is useful

regards

Pavel



> merlin
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/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