 Re: [Info-Ingres] Table procedure..its friday and I'm bored -
07-04-2011
, 03:43 AM
I've monkeyed with this further this morning and I would say that the tableproducing procedure must name the return columns, and as a consequence thereturn row needs to be named as well.
Marty
From: Paul Mason [mailto:Paul.Mason (AT) ingres (DOT) com]
Sent: 04 July 2011 09:12
To: Martin Bowes; Ingres and related product discussion forum
Subject: RE: [Info-Ingres] Table procedure..its friday and I'm bored
To be honest I'm not sure, but you referred to t.access in your select clause so I figured it needed to return something called 'access'. The fact youalready had an input parameter called access is probably what confused things.
Regards
Paul
From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 03 July 2011 09:36
To: Ingres and related product discussion forum
Cc: Paul Mason
Subject: RE: [Info-Ingres] Table procedure..its friday and I'm bored
Hi Paul,
Thanks heaps, thats working now.
I presume naming the row and the column is a requirement for Table Procedures unlike row-returning procedures?
Marty
From: Paul Mason [mailto:Paul.Mason (AT) ingres (DOT) com]
Sent: 01 July 2011 16:31
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Table procedure..its friday and I'm bored
You haven't defined the column name being returned, try this:
result row myrow ( access varchar(32) )
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Martin Bowes
Sent: 01 July 2011 15:41
To: Ingres and related product discussion forum
Subject: [Info-Ingres] Table procedure..its friday and I'm bored
Hi all,
So its Friday and I decided to read the manual because I was bored. Came across this interesting Table Procedure stuff which looked like fun.
Can anyone tell me why the following (rather silly example) isn't working....
\r
set autocommit on
\g
drop procedure table_proc;
\p\g
create procedure table_proc(
access integer not null not default
)
result row ( varchar(32) )
as
declare
set_access varchar(32) not null not default;
begin
FOR select case when access = 16 then 'private'
when access = 17 then 'public'
else 'unknown'
end into :set_access
DO
return row (:set_access);
ENDFOR;
return 0;
end;
\p\g
select d.name, d.own, d.access, t.access
from iidatabase d, table_proc (d.access) t
\p\g
\q
instead of producing something like:
iidbdb $ingres 17 public
I get:
iidbdb $ingres 17 1970274310
Martin Bowes
 |
Linear Mode
