[v10] CREATE TEMP FUNCTION/CREATE FUNCTION PG_TEMP.X
Hi all.
It seems I cannot use a temporary function.
I know there's no "CREATE TEMP FUNCTION".
But while I can do
tmp2=# CREATE FUNCTION pg_temp.x( OUT b BOOL )
language PLPGSQL
AS $L0$
BEGIN
b := TRUE;
END;
$L0$;
SET search_path TO pg_temp,"$user", public;
the following fails:
tmp2=# SELECT * FROM x();
LINE 1: select * from x();
^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.
tmp2=# \df+ x
List of functions
Schema | Name | Result data type | Argument data types | Type |
Volatility | Parallel | Owner | Security | Access privileges |
Language | Source code | Description
--------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
(0 rows)
tmp2=# \df+ pg_temp.x
List of functions
Schema | Name | Result data type | Argument data types | Type |
Volatility | Parallel | Owner | Security | Access privileges |
Language | Source code | Description
--------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
(0 rows)
but this succeeds:
tmp2=# select * from pg_temp.x();
b
---
t
(1 row)
I think I am doing/thinking something wrong.
But what?
TALIA!
--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS
Vincenzo Romano <vincenzo.romano@notorand.it> writes:
It seems I cannot use a temporary function.
You have to schema-qualify the temp function name when calling it, too.
regards, tom lane
2017-12-21 17:52 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Vincenzo Romano <vincenzo.romano@notorand.it> writes:
It seems I cannot use a temporary function.
You have to schema-qualify the temp function name when calling it, too.
regards, tom lane
Hi.
So search_path is not used with functions?
--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS
Vincenzo Romano <vincenzo.romano@notorand.it> writes:
2017-12-21 17:52 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
You have to schema-qualify the temp function name when calling it, too.
So search_path is not used with functions?
pg_temp is explicitly ignored when searching for functions/operators.
Otherwise, installing a trojan horse is just too easy.
regards, tom lane
2017-12-21 17:56 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Vincenzo Romano <vincenzo.romano@notorand.it> writes:
2017-12-21 17:52 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
You have to schema-qualify the temp function name when calling it, too.
So search_path is not used with functions?
pg_temp is explicitly ignored when searching for functions/operators.
Otherwise, installing a trojan horse is just too easy.regards, tom lane
I'm not sure whether this decision actually makes PG more scure.
But, anyway, thanks for the insight: I've just found the
documentations for this.
--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS
On Thu, Dec 21, 2017 at 12:07 PM, Vincenzo Romano <
vincenzo.romano@notorand.it> wrote:
2017-12-21 17:56 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Vincenzo Romano <vincenzo.romano@notorand.it> writes:
2017-12-21 17:52 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
You have to schema-qualify the temp function name when calling it, too.
So search_path is not used with functions?
pg_temp is explicitly ignored when searching for functions/operators.
Otherwise, installing a trojan horse is just too easy.regards, tom lane
I'm not sure whether this decision actually makes PG more scure.
But, anyway, thanks for the insight: I've just found the
documentations for this.--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS
Aside from the simple explanations you have received, I question your
justification for even having a temporary function.
Functions are only entries in the system catalogs and as such, take up just
a tiny amount of physical space. In addition,
if you ever need it again, you will have to expend time recreating it. Why
not just once and keep it?
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
2017-12-22 0:50 GMT+01:00 Melvin Davidson <melvin6925@gmail.com>:
On Thu, Dec 21, 2017 at 12:07 PM, Vincenzo Romano <vincenzo.romano@notorand.it> wrote:
2017-12-21 17:56 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
Vincenzo Romano <vincenzo.romano@notorand.it> writes:
2017-12-21 17:52 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
You have to schema-qualify the temp function name when calling it, too.
So search_path is not used with functions?
pg_temp is explicitly ignored when searching for functions/operators.
Otherwise, installing a trojan horse is just too easy.regards, tom lane
I'm not sure whether this decision actually makes PG more scure.
But, anyway, thanks for the insight: I've just found the
documentations for this.--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVSAside from the simple explanations you have received, I question your justification for even having a temporary function.
Functions are only entries in the system catalogs and as such, take up just a tiny amount of physical space. In addition,
if you ever need it again, you will have to expend time recreating it. Why not just once and keep it?
Hi.
Thanks for your comment.
The reason for having temporary object, in my current design, is to
have something shadowing something else on a per session basis, thanks
to the search_path variable.
It's not simply a matter or storage room or access speed. Not at all to me.
If you use, for example:
SET search_path to pg_temp,"$user",public;
you can put general stuff in public, per-user data in "$user" and per
session data in pg_temp.
Then the "name resolution" will follow the above priority during lookup.
And, as I put more and more logics in the DB, having temporary
functions gives me a simple, clean and yet powerful design.
As soon as my applications connect, they run SELECT * FROM
f_application_init( 'MYAPPNAME' ).
That function (which is not temporary) will setup the DB-level, the
user-level and the session-level stuff.
Currently it eats about 500 msec to run and it's run only once per session.
So, the answer to your question is: "why not if it can be useful
"
--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS