[PL/pgSQL] function call

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

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