Thread: [HACKERS] "SQL sentence"?
I'm confused by this: "pg_event_trigger_ddl_commands returns one row for each base command executed; some commands that are a single SQL sentence may return more than one row." What is a "SQL sentence"? (FWIW, I'm wondering because I was just looking to see why there's no details for things like altering a column in a table.) -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532)
Jim Nasby wrote: > I'm confused by this: > > "pg_event_trigger_ddl_commands returns one row for each base command > executed; some commands that are a single SQL sentence may return more than > one row." > > What is a "SQL sentence"? I meant "a single SQL command". The word "sentence" probably just slipped through. Will fix. > (FWIW, I'm wondering because I was just looking to see why there's no > details for things like altering a column in a table.) Do you mean you want to have access to the details of the alter table operations being executed? There's no structured data for that; you need to write a C function to examine the pg_ddl_command opaque column. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2/17/17 10:19 PM, Alvaro Herrera wrote: >> (FWIW, I'm wondering because I was just looking to see why there's no >> details for things like altering a column in a table.) > Do you mean you want to have access to the details of the alter table > operations being executed? There's no structured data for that; you > need to write a C function to examine the pg_ddl_command opaque column. Yeah. It doesn't seem unreasonable for a user to want to get at that info. Could the opaque column be mapped to a composite? I guess that'd be a bit of a pain due to the union. :/ And I have a suspicion that alterTable.subcmds is a list of CollectedCommand, making things more fun. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532)
Jim Nasby wrote: > On 2/17/17 10:19 PM, Alvaro Herrera wrote: > > > (FWIW, I'm wondering because I was just looking to see why there's no > > > details for things like altering a column in a table.) > > Do you mean you want to have access to the details of the alter table > > operations being executed? There's no structured data for that; you > > need to write a C function to examine the pg_ddl_command opaque column. > > Yeah. It doesn't seem unreasonable for a user to want to get at that info. Sure. We have the extension that turned the command into JSON. It's still an unfinished patch, sadly, even though Alex Shulgin spent a lot of effort trying to get it finished. It is still missing a nontrivial amount of work, but within reach ISTM. > Could the opaque column be mapped to a composite? No. If it could, we would just have added the fields to the output of the function. > I guess that'd be a bit of > a pain due to the union. :/ And I have a suspicion that alterTable.subcmds > is a list of CollectedCommand, making things more fun. The AT subcmds have their own struct, so it's even more fun than that. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2/17/17 10:46 PM, Alvaro Herrera wrote: > Jim Nasby wrote: >> On 2/17/17 10:19 PM, Alvaro Herrera wrote: >>>> (FWIW, I'm wondering because I was just looking to see why there's no >>>> details for things like altering a column in a table.) >>> Do you mean you want to have access to the details of the alter table >>> operations being executed? There's no structured data for that; you >>> need to write a C function to examine the pg_ddl_command opaque column. >> Yeah. It doesn't seem unreasonable for a user to want to get at that info. > Sure. We have the extension that turned the command into JSON. It's > still an unfinished patch, sadly, even though Alex Shulgin spent a lot > of effort trying to get it finished. It is still missing a nontrivial > amount of work, but within reach ISTM. I wonder if there's some generic way we could make C structs to JSON. IIRC there's other places where that ability would be handly. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532)
On 2/17/17 10:46 PM, Alvaro Herrera wrote: > Sure. We have the extension that turned the command into JSON. It's > still an unfinished patch, sadly, even though Alex Shulgin spent a lot > of effort trying to get it finished. It is still missing a nontrivial > amount of work, but within reach ISTM. You're speaking of https://www.postgresql.org/message-id/CACACo5Q_UXYwF117LBhjZ3xaMPyrgqnqE%3DmXvRhEfjJ51aCfwQ%40mail.gmail.com ? Can you reply to that to restart discussion? -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532)