Call function in PostgreSQL with all parameters from a query result

Question

I have a stored procedure that requires a few parameters:

CREATE OR REPLACE FUNCTION USP_GET_FAID(IN "IN_metricsId"   TEXT,
                                    IN "IN_shop"        TEXT,
                                    IN "IN_shopUserId"  TEXT,
                                    IN "IN_session"     TEXT DEFAULT NULL,
                                    IN "IN_ipHash"      TEXT DEFAULT NULL,
                                    IN "IN_shopSession" TEXT DEFAULT NULL,
                                    IN "IN_userAgent"   TEXT DEFAULT NULL) 
RETURNS INT8 AS $$

I'd like to call this stored procedure and to provide the parameters through the results of a SELECT query, so the Stored Procedure would run for each of the lines.
I don't care about what the function returns, so it can be ignored.

How is it possible to do it (if at all)?

What does the function return? Can you show us the complete function definition? Btw: using quoted identifiers is almost always a bad choice. They are much more trouble in the long run then they are worth it. — user330315
– user330315, Commented Feb 10, 2015 at 13:41
@a_horse_with_no_name: Thanks - fixed. And why using quoted identifiers is a bad choice? — Nils Tierecke
– Nils Tierecke, Commented Feb 10, 2015 at 13:45
@a_horse_with_no_name but otherwise it lowercases everything... if I use metricsId without, it'll be written as metricsid into the database. — Nils Tierecke
– Nils Tierecke, Commented Feb 10, 2015 at 13:48
Believe me: trying to use CamelCase is usually not a good idea: Use metrics_id instead (You can still write Metrics_ID if you want to). But if you insist on typing all those double quotes all the time, then I won't stop you - my experience is, that this usually has no benefit at all, but a lot of (hidden) problems in the long run. — user330315
– user330315, Commented Feb 10, 2015 at 13:50

user330315 · Accepted Answer · 2015-02-10 13:48:23Z

4

If the function only returns a single value and the parameters all come from a select statement, you can simply call it inside the select list:

select shop_id, usp_get_faid(metrics_id, shop, shop_id, shop_user_id, session, ip_hash, shop_session, user_agen)
from session_table

This assumes that all those columns are part of the session_table table.

answered Feb 10, 2015 at 13:48

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

pozs Over a year ago

OP doesn't care (ignores) the results, so this should be safe even if the function returns multiple values.

Ihor Romanchenko Over a year ago

Be careful with non VOLATILE functions. They may be optimized away. Also you may need to fetch all results of the query

Erwin Brandstetter Over a year ago

@IgorRomanchenko: Good point. Since this function is all about "side effects", it has to be VOLATILE to begin with (which is the default anyway).

Erwin Brandstetter · Accepted Answer · 2015-02-10 14:09:27Z

1

If all your input parameters come from a single table, you can simplify and just pass a single row type:

CREATE OR REPLACE FUNCTION usp_get_faid(_tbl my_tbl) 
  RETURNS int8 AS ...

Then:

SELECT usp_get_faid(t) FROM my_tbl t WHERE my_tbl_id = 123;

Doesn't matter if the table contains a few extra columns that are not actually needed in the function. Just use what you need ...

This actually works for any combination of columns, but you need a registered row type, and there is a row type registered for every table automatically.

answered Feb 10, 2015 at 14:09

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Call function in PostgreSQL with all parameters from a query result

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related