Alias for some code without CREATE FUNCTION?

Question

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?

the whole point of functions is to encapsulate code in a nice simple syntax. since you can't define functions, you'll just have to repeat your function's code everywhere. — Marc B
– Marc B, Commented Feb 24, 2015 at 21:07
@MarcB I know that's the whole point. That's what I want to use one for! I wondered if there was a way for psql to 'know' locally that when I say foo I want bar to happen - but crucially foo is not created server-side, since I do not have permission to do so. — OJFord
– OJFord, Commented Feb 24, 2015 at 21:09
I believe you can create a function in the pg_temp schema and it will disappear after the connection closes, but I couldn't tell you if you have access to that or not. — Bacon Bits
– Bacon Bits, Commented Feb 24, 2015 at 21:14
nope. you need functions, there's no other workaround. it'd be a pretty silly DB security system that restricts function creation, but has a completely identical "this_is_not_a_function" syntax that is allowed. — Marc B
– Marc B, Commented Feb 24, 2015 at 21:14
@MarcB Well, the point would be at DB side it's exactly the same - noone else could see/use it, I wouldn't be creating anything there. I could go overkill and write a function to replace foo with whatever and then run the query in any scripting language - that's not a flaw of anyone's security. — OJFord
– OJFord, Commented Feb 24, 2015 at 21:39

Clodoaldo Neto · Accepted Answer · 2015-02-24 21:29:40Z

2

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
;

edited Feb 24, 2015 at 21:29

answered Feb 24, 2015 at 21:11

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

OJFord Over a year ago

I've accepted this answer because it's probably best in the general case, although I'd actually arrived at my code by trying to remove sub-queries. In my case, creating the function in pg_temp schema as BaconBits suggested worked a treat.

Collectives™ on Stack Overflow

Alias for some code without CREATE FUNCTION?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related