Use DEFAULT or INTEGER value as a SQL function's argument

Question

I am making a SQL function to perform a smart insertion and manage a many to many table (a trigger wouldn't work as one value is variable).

Here is a minimum set for the example:

link_foo_to_bar (foo_id int, bar_id int)
foo (id serial, name character varying)

And here's my sql function:

CREATE FUNCTION smart_insert (bar_id integer, foo_id integer, name character varying name)
RETURNS void AS $body$
INSERT INTO foo VALUES (foo_id, name);
INSERT INTO link_foo_to_bar (CURRVAL('foo_id_seq'), bar_id);
$body$ LANGUAGE sql;

Now the problem is that foo_id is a serial so either it gets DEFAULT or an INTEGER as an incoming value. The function doesn't accept the DEFAULT keyword. How should I change the function to allow this varying argument? Using a text type?

Answer 1

Here's how you specify DEFAULT function arguments in PostgreSQL:

CREATE FUNCTION smart_insert (
  name character varying,
  bar_id integer, 
  foo_id integer = -1
)
RETURNS void AS $body$
INSERT INTO foo VALUES (
  CASE foo_id WHEN -1 THEN NEXTVAL('foo_id_seq') ELSE foo_id END, 
  name
);
INSERT INTO link_foo_to_bar (
  CASE foo_id WHEN -1 THEN CURRVAL('foo_id_seq') ELSE foo_id END,
  bar_id
);
$body$ LANGUAGE sql;

Here's how you can call it:

-- with an explicit foo_id:
smart_insert('abc', 1, 2);

-- applying the default value:
smart_insert('abc', 1);

... however, there's some useful feedback from Erwin that you should consider regarding inserting into serial types, which you should be careful with.

Answer 2

First of all, always provide explicit target columns for persisted INSERT statements. If you put that in a function and later change columns of the table, your function will either break, or worse not break and do unintended things.

Next, you should not enter values into a serial column to begin with. That leads to key collisions. You would need to get the underlying SEQUENCE back in sync with the currently highest value in the serial column, which can be tricky with concurrent write load. Just don't do it.

• Manually update the counter used for generation of primary key in Hibernate?

I suspect you really have a case of SELECT-or-INSERT. Meaning, if the given name already exists in foo, SELECT its associated foo_id, else INSERT a new row and use the dynamically assigned new foo_id.

The question does not seem to concern default values for function parameters so much as the keyword DEFAULT in SQL INSERT statements (completely different thing!) While you can use a function, you don't need a function for this.

Without concurrent write load

You could use a data-modifying CTE (with or without function):

WITH val(name, bar_id) AS (SELECT 'given_name'::varchar, 7)
     -- type cast needed for varchar, but numeric constant typed automatically
   , sel AS (SELECT f.foo_id, v.bar_id FROM foo f JOIN val v USING (name)
   , ins AS (INSERT INTO foo(name)
             SELECT name FROM val
             WHERE  NOT EXISTS (SELECT 1 FROM sel)  -- only if not found
             RETURNING foo_id)
INSERT INTO link_foo_to_bar(foo_id, bar_id)
SELECT foo_id, bar_id FROM sel
UNION  ALL
SELECT foo_id, bar_id FROM ins, val;

More details:

• PostgreSQL multi INSERT...RETURNING with multiple columns

Or if you really must sometimes force a manual foo_id ... This variant wrapped into an SQL function:

CREATE FUNCTION smart_insert(
   _name   varchar
 , _bar_id integer
 , _foo_id integer = NULL)  -- no default necessary, but NULL for convenience
RETURNS void AS
$func$
WITH sel AS (SELECT f.foo_id  -- existing foo_id overrules
             FROM   foo f WHERE f.name = _name)
   , ins AS (INSERT INTO foo(foo_id, name)
             SELECT COALESCE(_foo_id, nextval('foo_id_seq')), _name
             WHERE  NOT EXISTS (SELECT 1 FROM sel)  -- only if not found
             RETURNING foo_id)
INSERT INTO link_foo_to_bar(foo_id, bar_id)
SELECT foo_id, _bar_id FROM sel
UNION  ALL
SELECT foo_id, _bar_id FROM ins;
$func$  LANGUAGE sql;

Call ... like Lukas instructed.

Key element is COALESCE(_foo_id, nextval('foo_id_seq'): if you pass _foo_id it gets used, else the default is drawn from the sequence. Whatever you pick, it's returned with RETURNING foo_id and inserted down the line.

With concurrent write load

Postgres 9.4

You could use the function from this related answer that deals with the concurrency problem (and provides more explanation):

• Is SELECT or INSERT in a function prone to race conditions?

INSERT INTO link_foo_to_bar(foo_id, bar_id)
VALUES (f_foo_id('given_name'), 7);

Postgres 9.5

Still in development, but a couple of days ago the new UPSERT feature has been committed (yay!) and will be in the next release. But don't hold your breath, release is scheduled towards the end of 2015.

WITH val(name, bar_id) AS (SELECT 'given_name'::varchar, 7)
     -- type cast needed for varchar, but numeric constant typed automatically
   , ins AS (INSERT INTO foo(name)
             SELECT name FROM val
             ON CONFLICT (name) DO NOTHING  -- conflicting row is locked
             RETURNING foo_id)
INSERT INTO link_foo_to_bar(foo_id, bar_id)
SELECT foo_id, bar_id FROM ins, val;
UNION  ALL
SELECT f.foo_id, v.bar_id FROM foo f JOIN val v USING (name)
LIMIT  1;

Details in the devel manual:
http://www.postgresql.org/docs/devel/static/sql-insert.html

Aside: a trigger would almost certainly be possible, too. Not necessarily the better choice, though.