agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedhow to use function from different database
4+ messages / 3 participants
[nested] [flat]
* how to use function from different database
@ 2020-07-31 00:03 Bhupendra Babu <bbabu12@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Bhupendra Babu @ 2020-07-31 00:03 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Hi,
I created a generic function (DATEFIFF) login to postgres database using
psql. ANd I wanted to use across the entire instance , from all other
databases.
postgres=> CREATE FUNCTION DateDiff (units VARCHAR(30), start_t TIMESTAMP,
end_t TIMESTAMP)
postgres-> RETURNS INT AS $$
postgres$> DECLARE
postgres$> diff_interval INTERVAL;
...
...
grant execute on function DATEDIFF to public;
grant execute on function DATEDIFF(varchar,timestamp,timestamp) to public;
I tried both the grant one at a time, none of them works.
------------------------------------------
Connected to another database
\c application1
application1=> SELECT * from DATEDIFF('second', '2011-12-30
08:54:55'::timestamp, '2011-12-30 08:56:10'::timestamp);
ERROR: function datediff(unknown, timestamp without time zone, timestamp
without time zone) does not exist
LINE 1: SELECT * from DATEDIFF('second', '2011-12-30 08:54:55'::time...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
application1=>
------------------------------------------
How can I use this function across my entire postgresql instance from any
databases ?
--
Thanks.
Bhupendra B Babu
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to use function from different database
@ 2020-07-31 00:15 David G. Johnston <david.g.johnston@gmail.com>
parent: Bhupendra Babu <bbabu12@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2020-07-31 00:15 UTC (permalink / raw)
To: Bhupendra Babu <bbabu12@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Thu, Jul 30, 2020 at 5:04 PM Bhupendra Babu <bbabu12@gmail.com> wrote:
> I created a generic function (DATEFIFF) login to postgres database using
> psql. ANd I wanted to use across the entire instance , from all other
> databases.
>
> postgres=> CREATE FUNCTION DateDiff (units VARCHAR(30), start_t TIMESTAMP,
> end_t TIMESTAMP)
> How can I use this function across my entire postgresql instance from any
> databases ?
>
So far as a SQL user is concerned Individual databases are isolated from
each other. If you want to use the custom function in a database you need
to issue the "CREATE FUNCTION" command in that database.
See: https://www.postgresql.org/docs/12/manage-ag-overview.html
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to use function from different database
@ 2020-07-31 00:28 Rob Sargent <robjsargent@gmail.com>
parent: Bhupendra Babu <bbabu12@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Rob Sargent @ 2020-07-31 00:28 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
On 7/30/20 6:03 PM, Bhupendra Babu wrote:
> Hi,
>
> I created a generic function (DATEFIFF) login to postgres database using
> psql. ANd I wanted to use across the entire instance , from all other
> databases.
>
> postgres=> CREATE FUNCTION DateDiff (units VARCHAR(30), start_t
> TIMESTAMP, end_t TIMESTAMP)
> postgres-> RETURNS INT AS $$
> postgres$> DECLARE
> postgres$> diff_interval INTERVAL;
> ...
> ...
>
> grant execute on function DATEDIFF to public;
> grant execute on function DATEDIFF(varchar,timestamp,timestamp) to public;
>
> I tried both the grant one at a time, none of them works.
> ------------------------------------------
> Connected to another database
> \c application1
>
> application1=> SELECT * from DATEDIFF('second', '2011-12-30
> 08:54:55'::timestamp, '2011-12-30 08:56:10'::timestamp);
> ERROR: function datediff(unknown, timestamp without time zone,
> timestamp without time zone) does not exist
> LINE 1: SELECT * from DATEDIFF('second', '2011-12-30 08:54:55'::time...
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
> application1=>
> ------------------------------------------
>
> How can I use this function across my entire postgresql instance from
> any databases ?
>
> --
> Thanks.
> Bhupendra B Babu
You can create public.datadiff in the template1 database. Thereafter all
created databases in that cluster will get the function. However, for
existing databases, you'll need to apply the function by hand. I
recommend using the public schema explicitly. It should be fairly
straight forward to script this.
superuser privilege is of course required.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: how to use function from different database
@ 2020-07-31 06:05 Bhupendra Babu <bbabu12@gmail.com>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bhupendra Babu @ 2020-07-31 06:05 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; david.g.johnston@gmail.com; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Thanks Rob and David.
On Thu, Jul 30, 2020 at 5:28 PM Rob Sargent <robjsargent@gmail.com> wrote:
>
>
> On 7/30/20 6:03 PM, Bhupendra Babu wrote:
> > Hi,
> >
> > I created a generic function (DATEFIFF) login to postgres database using
> > psql. ANd I wanted to use across the entire instance , from all other
> > databases.
> >
> > postgres=> CREATE FUNCTION DateDiff (units VARCHAR(30), start_t
> > TIMESTAMP, end_t TIMESTAMP)
> > postgres-> RETURNS INT AS $$
> > postgres$> DECLARE
> > postgres$> diff_interval INTERVAL;
> > ...
> > ...
> >
> > grant execute on function DATEDIFF to public;
> > grant execute on function DATEDIFF(varchar,timestamp,timestamp) to
> public;
> >
> > I tried both the grant one at a time, none of them works.
> > ------------------------------------------
> > Connected to another database
> > \c application1
> >
> > application1=> SELECT * from DATEDIFF('second', '2011-12-30
> > 08:54:55'::timestamp, '2011-12-30 08:56:10'::timestamp);
> > ERROR: function datediff(unknown, timestamp without time zone,
> > timestamp without time zone) does not exist
> > LINE 1: SELECT * from DATEDIFF('second', '2011-12-30 08:54:55'::time...
> > ^
> > HINT: No function matches the given name and argument types. You might
> > need to add explicit type casts.
> > application1=>
> > ------------------------------------------
> >
> > How can I use this function across my entire postgresql instance from
> > any databases ?
> >
> > --
> > Thanks.
> > Bhupendra B Babu
> You can create public.datadiff in the template1 database. Thereafter all
> created databases in that cluster will get the function. However, for
> existing databases, you'll need to apply the function by hand. I
> recommend using the public schema explicitly. It should be fairly
> straight forward to script this.
>
> superuser privilege is of course required.
>
>
>
--
Thanks.
Bhupendra B Babu
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-07-31 06:05 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-31 00:03 how to use function from different database Bhupendra Babu <bbabu12@gmail.com>
2020-07-31 00:15 ` David G. Johnston <david.g.johnston@gmail.com>
2020-07-31 00:28 ` Rob Sargent <robjsargent@gmail.com>
2020-07-31 06:05 ` Bhupendra Babu <bbabu12@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox