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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general