pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDDL - TYPES
5+ messages / 4 participants
[nested] [flat]
* DDL - TYPES
@ 2024-02-08 19:14 Gambhir Singh <gambhir.singh05@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gambhir Singh @ 2024-02-08 19:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DDL - TYPES
@ 2024-02-08 20:02 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Gambhir Singh <gambhir.singh05@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2024-02-08 20:02 UTC (permalink / raw)
To: Gambhir Singh <gambhir.singh05@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DDL - TYPES
@ 2024-02-08 21:03 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2024-02-08 21:03 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DDL - TYPES
@ 2024-02-08 22:21 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2024-02-08 22:21 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DDL - TYPES
@ 2024-02-08 22:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2024-02-08 22:32 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-02-08 22:32 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-08 19:14 DDL - TYPES Gambhir Singh <gambhir.singh05@gmail.com>
2024-02-08 20:02 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-02-08 21:03 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-08 22:21 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-02-08 22:32 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox