Re: creating languages in the pg_catalog schema?

Just wondering if there are any downsides to creating a procedural
language in the pg_catalog schema instead of some other default schema.
I want the language to be available forever, regardless of schema drops
etc., to every user of the database, in every schema. And since I have
the impression that PostgreSQL (secretly) adds the pg_catalog schema to
the searchpath, it appears to be the obvious place to create the language.

Using the code below it appears to work, but are there any downsides?

CREATE FUNCTION pg_catalog.plpgsql_call_handler () RETURNS OPAQUE AS
'$libdir/plpgsql' LANGUAGE C;
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
HANDLER pg_catalog.plpgsql_call_handler;

Best wishes,

Jochem

Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)nl> writes:
> Just wondering if there are any downsides to creating a procedural
> language in the pg_catalog schema instead of some other default schema.

pg_dump will not dump it. Otherwise I don't think it matters.

regards, tom lane

Tom Lane wrote:
> Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)nl> writes:
>
>>Just wondering if there are any downsides to creating a procedural
>>language in the pg_catalog schema instead of some other default schema.
>
> pg_dump will not dump it. Otherwise I don't think it matters.

Worse. pg_dump will not dump at all. The error is:

Dumping database jochemd...
pg_dump: handler procedure for procedural language plpgsql not found
0 bytes.

Any workarounds?

Best wishes,
Jochem

Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)nl> writes:
> pg_dump: handler procedure for procedural language plpgsql not found

> Any workarounds?

Fix pg_dump.

There's probably not any good reason why pg_dump doesn't read in
definitions for all pg_proc entries, the same way it handles tables,
operators, etc.

regards, tom lane

Tom Lane wrote:
> Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)nl> writes:
>
>>pg_dump: handler procedure for procedural language plpgsql not found
>>Any workarounds?
>
> Fix pg_dump.
>
> There's probably not any good reason why pg_dump doesn't read in
> definitions for all pg_proc entries, the same way it handles tables,
> operators, etc.

I have never in my live coded a single byte in C. But I have found the
check in the sourcecode that throws this error and it looks like I can
just delete it so I will have a go at it.

Jochem

Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)nl> writes:
> I have never in my live coded a single byte in C. But I have found the
> check in the sourcecode that throws this error and it looks like I can
> just delete it so I will have a go at it.

Yeah, you could probably just "continue" instead of "exit_nicely()".
I don't like that as a permanent fix, but for a quick hack it will
no doubt serve your purposes.

regards, tom lane