Обсуждение: DDL - TYPES
Hi,
I was trying to generate DDL of functions and procedures and generated it successfully using the below query.
.\psql --no-align -t --host=xxxxxx --port=5432 --username=xxxx --dbname=xxxxx -c "SELECT pg_get_functiondef(f.oid) FROM pg_catalog.pg_proc f INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid) WHERE n.nspname = 'xxxxxx' AND f.proname = 'xxxxxx' order by 1" -o 'C:\Users\Gom\Desktop\ddl.sql'
When I tried to execute the generated DDL on other DB, I am getting the below error.
2024_02_07_IntegrationObjects_D0:3495: ERROR: type "xxxxxxxx" does not exist
I am trying to find a way to generate the DDL of the TYPE, but have not been successful. Please help to provide some solution so that I can get the DDL of all the functions and procedures with the coded TYPES.
Thanks & Regards
Gambhir Singh
On Fri, 2024-02-09 at 00:44 +0530, Gambhir Singh wrote: > I was trying to generate DDL of functions and procedures and generated it successfully > using the below query. > > [...] SELECT pg_get_functiondef(f.oid) [...] > > When I tried to execute the generated DDL on other DB, I am getting the below error. > > 2024_02_07_IntegrationObjects_D0:3495: ERROR: type "xxxxxxxx" does not exist > > I am trying to find a way to generate the DDL of the TYPE, but have not been successful. The canonical way is pg_dump --section=pre-data yourdbname | grep 'CREATE TYPE .*\.xxxxxxxx' Yours, Laurenz Albe
On Thu, Feb 8, 2024 at 3:02 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Fri, 2024-02-09 at 00:44 +0530, Gambhir Singh wrote:
> I was trying to generate DDL of functions and procedures and generated it successfully
> using the below query.
>
> [...] SELECT pg_get_functiondef(f.oid) [...]
>
> When I tried to execute the generated DDL on other DB, I am getting the below error.
>
> 2024_02_07_IntegrationObjects_D0:3495: ERROR: type "xxxxxxxx" does not exist
>
> I am trying to find a way to generate the DDL of the TYPE, but have not been successful.
The canonical way is
pg_dump --section=pre-data yourdbname | grep 'CREATE TYPE .*\.xxxxxxxx'
They're never multi-line?
On Thu, 2024-02-08 at 16:03 -0500, Ron Johnson wrote:
> > I am trying to find a way to generate the DDL of the TYPE, but have not been successful.
> >
> > The canonical way is
> >
> > pg_dump --section=pre-data yourdbname | grep 'CREATE TYPE .*\.xxxxxxxx'
>
> They're never multi-line?
Never, unless you create a type with a newline in its name:
CREATE TYPE "my
type" AS ENUM ('silly');
Yours,
Laurenz Albe
Laurenz Albe <laurenz.albe@cybertec.at> writes:
>> They're never multi-line?
> Never, unless you create a type with a newline in its name:
> CREATE TYPE "my
> type" AS ENUM ('silly');
I think Ron was pointing out that the creation command as a whole
could run to multiple lines --- which is true, as a quick look
at the pg_dump source code will confirm. dumpEnumType for example
will put a newline after each enum value. So you'd need something
smarter than grep to extract a usable result.
One idea is to use pg_restore -l and then -L to extract a single
dump object from an archive dump.
regards, tom lane