Re: [Postgres-xc-bugs] View type cannot be used from within function

[Michael P. <mic...@gm...>]

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