PostgreSQL: create function to be used a INSERT

Hello,

is it possible in PostgreSQL to create a function which can be used as an INSERT query.
All the examples I have found used the functions in a select query such
as

CREATE OR REPLACE FUNCTION myfunc() ...

which is then used as:

SELECT myfunc(foo) FROM sometable;

I would like to use a function instead of an insert query: Here is an extract of the function:

CREATE OR REPLACE FUNCTION insertInfo(arg1, ...
RETURNS opaque
AS 'DECLARE
   c_title ALIAS FOR $1;
   temp RECORD;
   titID RECORD;
   artID RECORD;
   albID RECORD;
   genID RECORD;

   BEGIN
      -- check if the artist already exists
      -- retrieve the number if so
      -- otherwise insert it and get the current sequence value
      SELECT INTO temp id FROM artist WHERE name = c_artist;
      IF NOT found THEN
         INSERT INTO artist (name) VALUES(c_artist);
         SELECT INTO artID lastvalue FROM artist;
      ELSE
         artID := temp.id;
      END IF;
   END;'
LANGUAGE 'plpgsql';

Using insertInfo(…); does not work.

Thanks,
Christian

This is just how functions are called. A function can do anything you want internally, but it must always be called as “SELECT myfunction([argument list]);”

It does NOT need to be called as “SELECT myfunction([argument list]) FROM mytable;”. In fact, I don’t know if that can work at all.

But anyway, if you call a function which inserts data in a table, you can just call it as SELECT myfunction(arg1, arg2, arg3), and the function can insert arg1, arg2, and arg3 into a row of the table. No problem at all.

I would like to use a function instead of an insert query: Here is an extract of the function:

CREATE OR REPLACE FUNCTION insertInfo(arg1, ...
RETURNS opaque
AS 'DECLARE
   c_title ALIAS FOR $1;
   temp RECORD;
   titID RECORD;
   artID RECORD;
   albID RECORD;
   genID RECORD;

   BEGIN
      -- check if the artist already exists
      -- retrieve the number if so
      -- otherwise insert it and get the current sequence value
      SELECT INTO temp id FROM artist WHERE name = c_artist;
      IF NOT found THEN
         INSERT INTO artist (name) VALUES(c_artist);
         SELECT INTO artID lastvalue FROM artist;
      ELSE
         artID := temp.id;
      END IF;
   END;'
LANGUAGE 'plpgsql';

Using insertInfo(…); does not work.

Have you tried “SELECT insertInfo(…)”? That should work. If not, what error message do you get? Another way you could handle things is to use a “before update” trigger which calls your function.

Also, you should check into the PostgreSQL concept of RULEs. A rule is something that can happen upon any type of query you decide in a table. It’s kind of like a trigger, but uses only regular SQL, without creating a function definition. For example you can have a RULE that happens upon INSERT to a table, which does something completely else with that data. It essentially allows you to internally redirect a query in any way you want, even splitting it into multiple other queries internally.

Thank you very much, that works :slight_smile:
The only problem is, that the function still has some errors … :blush:

The function body has to be enclosed within single quotes. But what can I write if I actually need single quotes inside, for example to get a value from a sequence? single quote single quote ?

-- inside a function
-- get the nextval from the sequence
SELECT nextval(''genre_id_seq'') INTO genID;

Also, you should check into the PostgreSQL concept of RULEs. A rule is something that can happen upon any type of query you decide in a table. It’s kind of like a trigger, but uses only regular SQL, without creating a function definition. For example you can have a RULE that happens upon INSERT to a table, which does something completely else with that data. It essentially allows you to internally redirect a query in any way you want, even splitting it into multiple other queries internally.

I had a look at the RULE concept but I am not sure if I totally understand it. I want to categorize some music files. The function should check for example if the artist already exists. If the artist does not exist, the function should insert the artist in the table and then retrieve the current sequence value.
There would be several checks and INSERTs necessary and the function is only run for the existing files or a new one.

Yes, a single quote can escape a single quote. Remember, then, that if you are escaping a section that already has two single quotes, you will need 4 single quotes.

Also, you can use regular doublequotes inside a single quoted function definition.

I had a look at the RULE concept but I am not sure if I totally understand it. I want to categorize some music files. The function should check for example if the artist already exists. If the artist does not exist, the function should insert the artist in the table and then retrieve the current sequence value.
There would be several checks and INSERTs necessary and the function is only run for the existing files or a new one.

The concept of rules is very simple. For complex logic, you are probably better off with a function, but rules provide nice ways to add simple additional logic to any table or view:

CREATE RULE [rule_name] AS ON INSERT TO [table_or_view_name] DO INSTEAD
(
   INSERT INTO [other_table] VALUES(new.col1, new.col2, new.col3);
   INSERT INTO [another_table] VALUES(new.col4, new.col5, new.col6)
);

This is a simple example of how you could make a joined view capable of receiving INSERTS and redirecting the right column arguments to the right base tables. Remember that “ON INSERT” can also be replaced with “ON UPDATE”, “ON DELETE”, or “ON SELECT”. Also, DO INSTEAD can be replaced with “DO ALSO” (meaning that your rule does not cancel the originating action, but simply does something additional).

For more info, read the following, and search around in the PostgreSQL documentation:

http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=sql-createrule.html
http://www.postgresql.org/docs/aw_pgsql_book/node124.html

How could you pass an array to function. Lets say an array of phone numbers.

I have opened a thread but no one seems to know: