Re: replicating DROP commands across servers

Jim Nasby wrote:
> On 10/6/14, 11:24 PM, Robert Haas wrote:
>
> Offlist.
>
> >>FWIW, I've run into situations more than once in userspace where I need a
> >>way to properly separate schema and object name. Generally I can make do
> >>using reg* casts and then hitting catalog tables, but it'd be nice if there
> >>was an easier way.
> >
> >Sure, although I think that's a bit of a separate problem. It's hard
> >to iterate through a string a character at a time from the SQL level
> >so that you can handle stuff like the quote_literal() rules. If we
> >want people to be able to do that easily we need to provide tools to
> >handle it. But C is actually quite well-suited to such tasks.
>
> Yeah, I wouldn't want to attempt this in SQL; I was saying that a
> built-in function to do this would be broadly useful, not just for
> replicating DROPs.

Well, most of what you need is served by pg_identify_object, I think:
just grab the OID from the appropriate catalog, and the OID of the
catalog itself; that function will give you schema and name, which is
what you need. Probably the most difficult part is figuring out which
reg* cast you want .. and of course there are also cases where there is
no such datatype to cast to in the first place.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services