How to create SQL function with parameter?

Question

I have an SQL function:

-- Function: insert_into_wgs()

-- DROP FUNCTION insert_into_wgs();

CREATE OR REPLACE FUNCTION insert_into_wgs()
  RETURNS void AS
$BODY$
BEGIN
INSERT INTO parcels (num,vid,support_num,name_dispatcher,cadastr,the_geom,status_id)  
SELECT num,vid,support_num,name_dispatcher,cadastr,ST_TRANSFORM(the_geom,4326),status_id
FROM parcels_temp
WHERE num NOT IN (SELECT num FROM parcels)AND deleted='no';
end;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION insert_into_wgs() OWNER TO postgres;

You see that it works only for parcels, parcels_temp tables. How can I create function with parameters insert_into_wgs(table, table_temp);?

Have you already tried `CREATE OR REPLACE FUNCTION insert_into_wgs(table, table_temp)`? — John Dvorak, Dec 13 '12 at 07:19
I dont know how this functions calls so cant ask my question to google( — Kliver Max, Dec 13 '12 at 07:21
You have to use dynamic SQL if you want to parameterize identifiers. And use `EXECUTE` in plpgsql, [similar to this answer](http://stackoverflow.com/questions/7914325/insert-with-dynamic-table-name-in-trigger-function/7915100#7915100). — Erwin Brandstetter, Dec 13 '12 at 07:32
I read this http://stackoverflow.com/questions/7914325/insert-with-dynamic-table-name-in-trigger-function/7915100#7915100 and cant understand what its mean. How its use dynamic table name? — Kliver Max, Dec 13 '12 at 07:34

score 2 · Accepted Answer · edited May 23 '17 at 10:24

2

As mentioned in the comment, you have to use dynamic SQL if you want to parametrize identifiers. And use EXECUTE in plpgsql.

In the base query better use NOT EXISTS instead of col NOT IN (<subquery>).

Now, if only the table names change and the columns stay the same, this simple demo would do the job:

CREATE OR REPLACE FUNCTION insert_into_tbl(_tbl text, _tbl_tmp text)
  RETURNS void AS
$func$
BEGIN

EXECUTE format($x$
INSERT INTO %I (...)  -- enter columns
SELECT ...            -- enter columns
FROM %I t
WHERE NOT EXISTS (
    SELECT 1
    FROM   %1$I p     -- reuse first table name
    WHERE  p.num = t.num
    )
AND   deleted = 'no'$x$, _tbl, _tbl_tmp);

END
$func$ LANGUAGE plpgsql;

Check out format() in the manual.
There are quite a few related answers on SO. Try this search.

edited May 23 '17 at 10:24

Community

1
1

answered Dec 13 '12 at 07:49

Erwin Brandstetter

605,456
145
1,078
1,228

In Postgres i dont get errors. Now try call this function in java) – Kliver Max Dec 13 '12 at 08:02
@KliverMax: Note I fixed the `WHERE` condition to match your example. – Erwin Brandstetter Dec 13 '12 at 08:08
I get a error : `org.postgresql.util.PSQLException: ERROR: column "parcels" does not exist`. In java i call function : `java.sql.CallableStatement proc = ce_proc.prepareCall("{call insert_into_wgs("+t_name+","+t_temp+")}")` – Kliver Max Dec 13 '12 at 08:14
@KliverMax: My bad, I forgot to parametrize the second occurrence of the table name. – Erwin Brandstetter Dec 13 '12 at 08:23
@KliverMax: Well, then `column "parcels"` probably `does not exist`. You may have missed my assertion: `if only the table names change and the columns stay the same`. You can build column names into the query, too. You need to query `pg_catalog.pg_attribute` or the information_schema for this. I posted a couple of related answers in the past. But no more time now. – Erwin Brandstetter Dec 13 '12 at 08:31
Yeah im really stupid. Foggot about columns( But thanx for help. – Kliver Max Dec 13 '12 at 08:58

How to create SQL function with parameter?

1 Answers1