Re: [HACKERS] Parser - Query Analyser
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parser - Query Analyser
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parser - Query Analyser
On Nov 17, 2012, at 9:18, Michael Giannakopoulos miccagi...@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 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parser - Query Analyser
Michael Giannakopoulos miccagi...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Parser - Query Analyser
- Цитат от Michael Giannakopoulos (miccagi...@gmail.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
