Re: 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(at)gmail(dot)com> 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];
>

Anything is possible but what you are trying to do makes little sense generally and would take a tremendous amount of work to be done in PostgreSQL. The two main limitations are that you are creating a entirely new query language format and that 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 functionality and/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 would get you better advice. As to learning how to contribute to the project I will let others point you to the existing resources that are out there. It would, however, probably help to explain what skills and background you already posses.

David J.

Michael Giannakopoulos <miccagiann(at)gmail(dot)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(at)gmail(dot)com), на 17.11.2012 в 16:18 -----

> 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];
>

Why invent non-standard syntax for something that you could do in SQL.

You could try something like this:

SELECT
(m.f).f_out_name1 AS out1,
(m.f).f_out_name2 AS out2,
(m.f).f_out_name3 AS out3
FROM (
SELECT f(att1,att2,att3) FROM input_table_name
) AS m;

Best regards

--
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!
>

Michael:

- Please reply in-line with posts to preserve the thread of the
conversation. It makes it easier for others to understand.

- Reply to the mailing list, not directly to me. Use "Reply list" if
your mail client supports it, or "Reply to all" otherwise.

I've moved this conversation to pgsql-general. There is no sign that
it's a development issue at this point. Please follow up here, not on
pgsql-hackers.

If you wish to follow this up, please provide a DETAILED EXAMPLE showing
what you are trying to do, as I explained in my previous post. Table
definitions, ample data, desired output, detailed explanation of how you
get from the input data to the desired output, etc.list

--
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