From: | Bhupendra Babu <bbabu12(at)gmail(dot)com> |
---|---|
To: | Rob Sargent <robjsargent(at)gmail(dot)com>, david(dot)g(dot)johnston(at)gmail(dot)com |
Cc: | pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | Re: how to use function from different database |
Date: | 2020-07-31 06:05:54 |
Message-ID: | CAOEE2FeZdAmSgamTmnPtO8SbfxADB2UNB8EnUJcah6eXnfT=Yw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Thanks Rob and David.
On Thu, Jul 30, 2020 at 5:28 PM Rob Sargent <robjsargent(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Samed YILDIRIM | 2020-07-31 07:07:05 | Re: foreign key referencing inheritance parent |
Previous Message | Rob Sargent | 2020-07-31 00:28:44 | Re: how to use function from different database |