pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction with a composite type calling another function - Mission Impossible?
3+ messages / 2 participants
[nested] [flat]
* function with a composite type calling another function - Mission Impossible?
@ 2004-06-02 14:52 Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Riccardo G. Facchini @ 2004-06-02 14:52 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: function with a composite type calling another function - Mission Impossible?
@ 2004-06-02 15:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-06-02 15:43 UTC (permalink / raw)
To: Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>; +Cc: pgsql-sql
"Riccardo G. Facchini" <abief_ag_-postgresql@yahoo.com> writes:
> declare my_new_complex complex_number;
> select * from complex_to_complex(my_new_complex) into my_result;
> ERROR: column "my_new_complex" does not exist
plpgsql doesn't presently cope with passing whole-row variables into SQL
expressions, which is essentially what you've got here. There's some
chance it will work in time for 7.5.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: function with a composite type calling another function - Mission Impossible?
@ 2004-06-02 15:55 Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Riccardo G. Facchini @ 2004-06-02 15:55 UTC (permalink / raw)
To: pgsql-sql
--- Tom Lane <__> wrote:
> "Riccardo G. Facchini" <__> writes:
> > declare my_new_complex complex_number;
>
> > select * from complex_to_complex(my_new_complex) into my_result;
>
> > ERROR: column "my_new_complex" does not exist
>
> plpgsql doesn't presently cope with passing whole-row variables into
> SQL
> expressions, which is essentially what you've got here. There's some
> chance it will work in time for 7.5.
>
> regards, tom lane
then, why
---
select * from complex_to_real(real_to_complex(1, 2));
select * from complex_to_complex(real_to_complex(1, 2));
---
work? and why the function definition:
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;
---
doesn't make a difference?
thanks for your quick answer,
=====
Riccardo G. Facchini
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-06-02 15:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-02 14:52 function with a composite type calling another function - Mission Impossible? Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
2004-06-02 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-02 15:55 ` Riccardo G. Facchini <abief_ag_-postgresql@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox