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?

user330315 · Accepted Answer · 2019-03-15 14:21:50Z

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, 2019 at 14:21

user330315

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

2 Comments

Diego Victor de Jesus Over a year ago

The query without a subquery and LIMIT would be the best case from my perspective

user330315 Over a year ago

@DiegoVictordeJesus: but it does something different than the intended query from the question.

Gordon Linoff · Accepted Answer · 2019-03-15 14:21:37Z

0

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;

answered Mar 15, 2019 at 14:21

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Use dynamic parameter in SELECT query in Postgres?

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related