CODEWITHSUNDEEP
sqlpostgresqlstored-procedures
Joe Attardi
Joe Attardi

Reputation: 4521

Use stored procedure return value in both select and where clause, without having to call it twice?

I have a stored procedure that returns a single value. I want to do a SELECT query that both selects the stored procedure's return value, as well as use that value in the WHERE clause.

I tried doing this:

SELECT u.name, myproc(u.id) as p FROM users WHERE p = 'something';

But that didn't work, at least, not on PostgreSQL. I got the error: ERROR: column "p" does not exist

The only way I could get this to work is call the procedure twice, e.g.

SELECT u.name, myproc(u.id) FROM users WHERE myproc(u.id) = 'something';

But that seems wasteful and unnecessary to call the function twice. Is there any way to do this without calling the function twice?

Upvotes: 1

Views: 265

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

This is cleaner than a CTE or a subquery

select name, p
from
    users
    cross join
    myproc(id) m(p)
where p = 'something';

Also a CTE is an optimization barrier to the planner. If you opt for a separate query do it in a subquery.

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

You could use a common table expression (or a subquery);

WITH cte AS (
  SELECT u.name, myproc(u.id) as p
  FROM users u
)
SELECT * FROM cte WHERE p = 'something'

Basically, a common table expression works like a subquery, you do a SELECT and give the result a name, then you can select from the result as if it were a table.

Upvotes: 1

Related Questions