agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@lists.postgresql.org
Subject: Re: how to use function from different database
Date: Thu, 30 Jul 2020 18:28:44 -0600
Message-ID: <582718df-7d99-5601-675c-bf2125d2e689@gmail.com> (raw)
In-Reply-To: <CAOEE2Fc9G2A92SzdghvJE06YOS=XL-mU8184ftDGn30a73Y31Q@mail.gmail.com>
References: <CAOEE2Fc9G2A92SzdghvJE06YOS=XL-mU8184ftDGn30a73Y31Q@mail.gmail.com>
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.
view thread (4+ messages) latest in thread
Message-ID: <582718df-7d99-5601-675c-bf2125d2e689@gmail.com>
Permalink: ../582718df-7d99-5601-675c-bf2125d2e689@gmail.com/
Also on: postgresql.org/message-id/582718df-7d99-5601-675c-bf2125d2e689@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robjsargent@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: how to use function from different database
In-Reply-To: <582718df-7d99-5601-675c-bf2125d2e689@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox