Thread: Parser - Query Analyser
Hello guys,
My name is Michail Giannakopoulos and I am a graduate student at University of Toronto. I have no previous experience in developing a system like postgreSQL before.
What I am trying to explore is if it is possible to extend postgreSQL in order to accept queries of the form:
Select function(att1, att2, att3) AS output(out1, out2, ..., outk) FROM [database_name];
where att1, att2, att3 are attributes of the relation [database_name] while output(out1, out2, out3) expresses the output that comes from 'function' and the fields that this output should have are (out1, out2, out3). What I mean is that this kind of query should return tuples that obay to the 'output(...)' schema and are produced by processing the original tuples of 'database_name' relation in attributes att1, att2 and att3.
From what I have seen I need to teak both the parser and the query analyser in order to accept this form of query. However, I do not know how to tweak these systems. Even worse I do not know where to begin from.
The manual of extending SQL did not helped me and currently I am debugging postgreSQL source code. I would appreciate if you could provide me with more resources and correct ways of how to hack inside postgreSQL files and system. For example, where do I add a new parse node, where should I change the the grammar.
Thank you very much for all your time and all your help,
Michail
On Nov 17, 2012, at 9:18, Michael Giannakopoulos <miccagiann@gmail.com> wrote: > Hello guys, > > My name is Michail Giannakopoulos and I am a graduate student at University of Toronto. I have no previous experience indeveloping a system like postgreSQL before. > > What I am trying to explore is if it is possible to extend postgreSQL in order to accept queries of the form: > > Select function(att1, att2, att3) AS output(out1, out2, ..., outk) FROM [database_name]; > Anything is possible but what you are trying to do makes little sense generally and would take a tremendous amount of workto be done in PostgreSQL. The two main limitations are that you are creating a entirely new query language format andthat the name of the database is constant and determined at the time of connection to the database. From a practical perspective I do not believe it (as written exactly above) can done without breaking existing functionalityand/or introducing ambiguities. As I am not a PostgreSQL developer myself I cannot be of much more help but ISTM that providing more why and less what wouldget you better advice. As to learning how to contribute to the project I will let others point you to the existingresources that are out there. It would, however, probably help to explain what skills and background you alreadyposses. David J.
Michael Giannakopoulos <miccagiann@gmail.com> writes:
> What I am trying to explore is if it is possible to extend postgreSQL in
> order to accept queries of the form:
> Select function(att1, att2, att3) AS output(out1, out2, ..., outk) FROM
> [database_name];
> where att1, att2, att3 are attributes of the relation [database_name] while
> output(out1, out2, out3) expresses the output that comes from 'function'
> and the fields that this output should have are (out1, out2, out3).
You're not being terribly clear about what you intend this to mean,
but the standard interpretation of AS is that it just provides a column
renaming and doesn't for instance change datatypes. If that's what you
have in mind then it can be done today using AS in the FROM clause:
select * from foo() AS output(out1, out2, ...);
That doesn't allow passing data from a table to the function, but as of
HEAD we have LATERAL, so you could do
select output.* from tab, LATERAL foo(att1, att2) AS output(out1, out2, ...);
If you really insist on doing the renaming within a single composite
column in the SELECT output list then you're going to have a lot of
issues. Column name aliases are normally only associated with RTEs
(FROM-list entries) and SELECT output columns. Column names for columns
of a composite data type are properties of the type and so are out of
the reach of AS-renaming in the current system design. I think you'd
have to cons up an anonymous record type and treat the AS as an implicit
cast to that type. Seems like an awful lot of work in order to have a
nonstandard way to do something that can be done already.
regards, tom lane
----- Цитат от Michael Giannakopoulos (miccagiann@gmail.com), на 17.11.2012 в 16:18 -----<br /><br />> Hello guys,<br/>> <br />> My name is Michail Giannakopoulos and I am a graduate student at University<br />> of Toronto.I have no previous experience in developing a system like<br />> postgreSQL before.<br />> <br />> WhatI am trying to explore is if it is possible to extend postgreSQL in<br />> order to accept queries of the form:<br/>> <br />> Select function(att1, att2, att3) AS output(out1, out2, ..., outk) FROM<br />> [database_name];<br/>> <br /><br />Why invent non-standard syntax for something that you could do in SQL. <br /><br />Youcould try something like this:<br /><br />SELECT<br /> (m.f).f_out_name1 AS out1,<br /> (m.f).f_out_name2 AS out2,<br/> (m.f).f_out_name3 AS out3<br />FROM (<br /> SELECT f(att1,att2,att3) FROM input_table_name<br />) AS m;<br /><br/>Best regards<br /><br />--<br />Luben Karavelov
On 11/17/2012 10:18 PM, Michael Giannakopoulos wrote: > Hello guys, > > My name is Michail Giannakopoulos and I am a graduate student at > University of Toronto. I have no previous experience in developing a > system like postgreSQL before. > > What I am trying to explore is if it is possible to extend postgreSQL > in order to accept queries of the form: > > Select function(att1, att2, att3) AS output(out1, out2, ..., outk) > FROM [database_name]; I think you meant "FROM [table_name]". It certainly seems like it, as you describe "database_name" as a "relation" a little later. If you really meant "FROM [database_name]", you're not going to have much luck. PostgreSQL backends are associated with a single database. They cannot query across databases without hacks like dblink, which internally opens a connection to another backend. So you really can't query "FROM [database_name]", you must connect to a database then issue queries against it. If you meant "FROM relation_name": it sounds like you are describing a stored procedure that returns SETOF RECORD. If so, you can already do this, though the syntax is a little different. You have to pass the relation *name* or regclass oid into the procedure, where it builds a dynamic SQL statement to SELECT from the table and return the result. Alternately: Are you trying to describe a *row filter function*? Like a WHERE clause wrapped up in a function? It would really help if you could show some mock examples of what you're trying to achieve. Inputs as CREATE TABLE and INSERT statements, mock output, explanation of how you'd get that output, what problem you're trying to solve, etc. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 11/18/2012 09:57 PM, Michael Giannakopoulos wrote: > Hi guys, > > Thanks for your answers. Yes, what I meant is to create a function > that takes as an input rows of a specific relation, does something and > returns as an output rows with different attributes. I am > experimenting right now with the 'CREATE TYPE' and 'CREATE FUNCTION' > commands in order to see what I can get out of them! I have replied on pgsql-general. Please do not top-post, and reply to the list not directly to me. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services