Re: Extract especific text from a sql statement

Melvin Davidson-5 wrote
> You already have most of the result columns, so the following should do
> it.
>
> SELECT pc.cod,
> pc.val,
> pi.qtd,
> COALESCE(pc.name, 'empty') AS name,
> lower(coalesce(pc.email, 'empty')) as email,
> status,
> c1.relname,
> c2.relname,
> pc.startdate
> FROM pc
> INNER JOIN pi on (pc.cod = pi.cod)
> JOIN pg_class c1 ON (c1.relname = 'pc' AND c1.relkind = 'r'
> JOIN pg_class c2 ON (c2.relname = 'pi' AND c2.relkind = 'r'
> WHERE pc.startdate > CURRENT_DATE
> order by 1 desc;
>
> Learn the catalogs and you will learn to be a good dba.
>
> Melvin Davidson

I read the example answer as being a single "column" (or some other
println(...) output) that generates a single row for each of the string
literal identifiers extracted from a parse of the raw query text - possibly
after capturing raw identifiers and performing catalog lookups.

Your particular answer also seems pointless in that the JOIN pg_class is
unnecessary since the ON clause sets a constant for relname and then re-uses
that in the select-list. You'd been better off just writing: SELECT ...,
'pc' AS relname, 'pi' AS relname FROM ... and providing disambiguating
aliases.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Extract-especific-text-from-a-sql-statement-tp5821256p5821328.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.