From: Michael P. <mic...@gm...> - 2012-06-20 00:40:11
|
On Tue, Jun 19, 2012 at 4:41 PM, Dimitrije Radojevic <tem...@gm...>wrote: > Hi, > > I have encountered a problem when using view types within a function. I > read the documentation on VIEW to check if this functionality is not yet > implemented in Postgres-XC, but I couldn't find it, so I'm guessing this is > supposed to be a bug report (though I am not sure, this may just be a > missing feature). Here's my test case: > > 1) create a table > CREATE TABLE test_table ( > id SERIAL NOT NULL, > other_id int4 NOT NULL, > PRIMARY KEY("id") ) > DISTRIBUTE BY REPLICATION; > > 2) create a view > > > 3) create a function using the view > CREATE OR REPLACE FUNCTION some_function() RETURNS SETOF some_view AS > $body$ > BEGIN > RETURN QUERY SELECT * FROM some_view; > END; > $body$ > LANGUAGE 'plpgsql' > VOLATILE > CALLED ON NULL INPUT > SECURITY INVOKER > COST 100; > > I get the following error: > ERROR: type "some_view" does not exist > > This runs fine in postgres 9.1.4. > A view is only created on Coordinators. It is basically a projection of the data that is located on remote Datanodes, so selecting data from a view means that you need to fallback to a given table (or tables for a join), and to analyse the distribution type of those tables before fetching the data from Datanodes. So it doesn't really make sense to define view on Datanodes. When creating a function, we create it on all the nodes, resulting in the error you see as the process fails at the function creation. This indeed looks like a bug, and based on the definition XC has about views, it would make sense to create such functions only on Coordinators. As you are giving a test case so it will be easier to fix this particular function creation. -- Michael Paquier http://michael.otacoo.com |