Array of Composite Type as Stored Procedure parameter.

["John Tulodziecki" <johnt@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Hi PgSQL Users,

 

I need to define an array of composite types namely “rc_list” in the following context.

 

CREATE TYPE gui_ep_rc_action_type AS (

   route_action VARCHAR,

   reject_code VARCHAR,

   da_cdpn_strip INT2,

   da_cdpn_prefix VARCHAR,

   da_cdpn_replace VARCHAR,

   da_cdpn_noa VARCHAR,

   da_cgpn_strip INT2,

   da_cgpn_prefix VARCHAR,

   da_cgpn_replace VARCHAR,

   da_cgpn_noa VARCHAR);

 

CREATE TYPE gui_ep_rc_match_type AS (

   df_cdpn_match VARCHAR,

   df_cdpn_noa_match VARCHAR,

   df_cgpn_match VARCHAR,

   df_cgpn_noa_match VARCHAR,

   bearer_name VARCHAR,

   ip_filters INET[],

   time_filter VARCHAR);

 

CREATE TYPE gui_ep_rc_type AS (

   rc_name VARCHAR,

   rc_match gui_ep_rc_match_type,

   rc_action gui_ep_rc_action_type,

   rc_default_action gui_ep_rc_action_type);

 

CREATE TYPE gui_ep_rcg_type AS (

   inherited BOOL,

   rcg_name VARCHAR,

   rc_list gui_ep_rc_type[]);

 

CREATE TYPE gui_ep_add_type AS (

   endpoint_name VARCHAR,

   protocol INT2,

   ip_address INET,

   port INT2,

   sip_protocol VARCHAR,

   h323_protocol VARCHAR,

   incoming_group VARCHAR,

   outgoing_group VARCHAR[],

   reattempt BOOl,

   max_reattempts INT4,

   sip_reattempt_codes VARCHAR[],

   h323_reattempt_codes VARCHAR[],

   ic_routing_criteria gui_ep_rcg_type,

   og_routing_criteria gui_ep_rcg_type);

 

But this is rejected by PgSQL.

 

I have read that putting an underscore as follows should help …

 

rc_list _gui_ep_rc_type

 

but this too is rejected by PgSQL.

 

Can anyone please tell me how to correctly declare it and what syntax I need to submit a query to a stored procedure of this type ?

Is it simply {{a1,b1,c1,d1},{a2,b2,c2,d2} …} etc … ????

 

Thanks !!!.

 

 

 

But this also is rejected by PgSQL.

 

John Tulodziecki

Senior Software Engineer
Squire Technologies Ltd

 

Phone  +44(0)1305 757315
Web    www.squire-technologies.com
Email  johnt@xxxxxxxxxxxxxxxxxxxxxxxxx

 

NOTICE AND DISCLAIMER:

 

Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of Squire Technologies Ltd. If you have received this email in error please notify the sender immediately and delete this email from your system without copying or disseminating it or placing any reliance upon its contents.

 

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.0/249 - Release Date: 02/02/2006