CODEWITHSUNDEEP
sqlpostgresql
OJFord
OJFord

Reputation: 11140

Alias for some code without CREATE FUNCTION?

I have a query that makes repeated use of the same snippet of code, which I'll call foo(), as if the function existed:

SELECT
    foo(a),
    BUILTIN(foo(a))
FROM 
    foobar
GROUP BY
    foo(a)
HAVING
    BUILTIN(foo(a)) > bar()
ORDER BY
    foo(a)
;

Usually, I would have created this function foo long ago - what I actually see in my editor is the same messy nest of built-in functions several times.

However - I cannot CREATE my nice foo, as I only have USAGE permissions.

Does there exist a way for me to create some kind of 'alias', such that whenever I say foo, a longer function is executed; but this would of course not be available to any other DB user?

Upvotes: 1

Views: 43

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

In the case of the example you posted compute foo(a) in a subquery:

select foo_a, builtin(foo_a)
from (
    select foo(a) as foo_a
    from foobar
) s
group by 1
having builtin(foo_a) > bar()
order by 1
;

Upvotes: 2

Related Questions