function with a composite type calling another function - Mission Impossible?

From: "Riccardo G(dot) Facchini" <abief_ag_-postgresql(at)yahoo(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: function with a composite type calling another function - Mission Impossible?
Date: 2004-06-02 14:52:54
Message-ID: 20040602145254.44625.qmail@web13906.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi. I'm trying to work a fairly complex set of functions that use a
composite type for internal usage. This composite type should be passed
to a specific function that takes care of some housekeeping actions...
I haven't been able to make them work.

as an example, I've generated a series of silly functions, to figure
out where the problem lies:
---
CREATE or replace FUNCTION public.real_to_complex(float8, float8)
RETURNS complex_number AS
'declare my_real alias for $1;
declare my_complex alias for $2;

declare my_result complex_number;

begin

my_result.real := my_real;
my_result.complex := my_complex;

return my_result;

end;'
LANGUAGE 'plpgsql' VOLATILE;
CREATE or replace FUNCTION public.complex_to_real(complex_number)
RETURNS float8 AS
'declare my_complex alias for $1;

declare my_result float;

begin

my_result := my_complex.real + my_complex.complex;

return my_result;

end;'
LANGUAGE 'plpgsql' VOLATILE;
CREATE or replace FUNCTION public.complex_to_complex(complex_number)
RETURNS complex_number AS
'declare my_complex alias for $1;

declare my_result complex_number;

begin

my_result.real := my_complex.real * 2;
my_result.complex := my_complex.complex * 2;
return my_result;

end;'
LANGUAGE 'plpgsql' VOLATILE;

CREATE or replace FUNCTION public.really_complex(float, float) RETURNS
complex_number AS
'declare my_real alias for $1;
declare my_complex alias for $2;

declare my_result complex_number;

begin

select * from real_to_complex(my_real, my_complex) into my_result;

return my_result;

end;'
LANGUAGE 'plpgsql' VOLATILE;

CREATE or replace FUNCTION public.really_really_complex(complex_number)
RETURNS complex_number AS
'declare my_complex alias for $1;

declare my_new_complex complex_number;

declare my_result complex_number;

begin

select 1, 2 into my_new_complex.real, my_new_complex.complex;
my_new_complex.real := my_complex.real;
my_new_complex.complex := my_complex.complex;

select * from complex_to_complex(my_new_complex) into my_result;

return my_result;

end;'
LANGUAGE 'plpgsql' VOLATILE;
--

the following selects work normally:
---
select * from real_to_complex(1, 2);

select * from complex_to_real(real_to_complex(1, 2));

select * from complex_to_complex(real_to_complex(1, 2));

select * from really_complex(1, 2);
---

then, why this one doesn't work?
---
select * from really_really_complex(real_to_complex(1, 2));

result:

ERROR: column "my_new_complex" does not exist
CONTEXT: PL/pgSQL function "really_really_complex" line 13 at select
into variables

--

I tried some other forms for the function really_really_complex... the
answer is always the same:
---version 1
CREATE or replace FUNCTION public.really_really_complex(complex_number)
RETURNS complex_number AS
'declare my_complex alias for $1;

declare my_result complex_number;

begin

select * from complex_to_complex(my_complex) into my_result;

return my_result;

end;'
LANGUAGE 'plpgsql' VOLATILE;
--- version 2
CREATE or replace FUNCTION public.really_really_complex(complex_number)
RETURNS complex_number AS
'declare my_complex alias for $1;

declare my_result complex_number;

begin

my_result := complex_to_complex(my_complex);

return my_result;

end;'
LANGUAGE 'plpgsql' VOLATILE;
--

any suggestion/idea? what am I doing wrong?

=====
Riccardo G. Facchini

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2004-06-02 15:43:59 Re: function with a composite type calling another function - Mission Impossible?
Previous Message Rajesh Kumar Mallah 2004-06-02 13:48:17 Re: assistance on self join pls