Use dynamic parameter in SELECT query in Postgres?

Question

There is another question with this title - sorry to duplicate, but I simply cannot understand the answers to that question, so I assume plenty of others won't either and it will be useful to have a clear answer.

I am working in Postgres. I have a stored procedure called duplicate_project that takes a single parameter, the project ID.

I'd like to add a dynamic parameter in a WHERE clause in a SELECT query, to avoid having to hard-code a number.

So I'd like to avoid hardcoding:

SELECT duplicate_project(578);

And instead have something like this:

SELECT duplicate_project(SELECT project_id FROM folder WHERE name='foo' LIMIT 1);

Obviously this won't work, but is there a straightforward way to do something like it?

score 1 · Accepted Answer · answered Mar 15 '19 at 14:21

1

Do you mean something like this:

select duplicate_project(f.project_id)
from folder f
where name = 'foo'
limit 1;

The limit is not really needed. In that case you get multiple rows that would show the result of duplicate_project() for each project_id returned.

Or:

SELECT duplicate_project( (SELECT project_id FROM folder WHERE name='foo' LIMIT 1) );

answered Mar 15 '19 at 14:21

The query without a subquery and LIMIT would be the best case from my perspective – Diego Victor de Jesus Mar 15 '19 at 14:40
@DiegoVictordeJesus: but it does something different than the intended query from the question. – Mar 15 '19 at 14:41

score 0 · Answer 2 · answered Mar 15 '19 at 14:21

One method would be a scalar subquery:

SELECT duplicate_project( (SELECT project_id FROM folder WHERE name='foo' LIMIT 1) );

Another would be to use a FROM clause:

SELECT duplicate_project(x.project_id)
FROM (SELECT project_id FROM folder WHERE name = 'foo' LIMIT 1) x;

Use dynamic parameter in SELECT query in Postgres?

2 Answers2