Thread: Extract especific text from a sql statement

Emanuel Araújo wrote
> Hi,
>
> I need help to extract fields and tables from a sql statement.
>
> Example:
>
> SELECT pc.cod, pc.val, pi.qtd,
> COALESCE(pc.name, 'empty') AS name, lower(coalesce(pc.email, 'empty')) as
> email,
> status
> FROM pc
> INNER JOIN pi on (pc.cod = pi.cod)
> WHERE pc.startdate > CURRENT_DATE
> order by 1 desc
> ;
>
> I need to return that:
>
> pc.cod
> pc.val
> pi.qtd
> pc.name
> pc.email
> status
> pc
> pi
> pc.startdate
>
>
>
> can you help me ?

Me, probably not.  I would suggest you tell us what goal/use-case you have
in mind that you think you need to do the above.

The project is open source so you are welcome to look at the parser code and
see how it goes about pulling out identifiers and determining what they are.

A big question is how do you want to deal with aliases and views present in
the query?

Do you have a particular language you are restricting your search to?

David J.




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

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.