Thread: [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

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 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?
>

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