Book Image

PostgreSQL Server Programming

Book Image

PostgreSQL Server Programming

Overview of this book

Table of Contents (21 chapters)
PostgreSQL Server Programming Second Edition
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Preface
Index

Passing composite-type arguments


A composite-type, such as a table, or a user-defined type is passed to the PL/Tcl function as an associative array (Hash table). The attribute names of the composite-type are the element names in the array. Attributes with NULL values are not available in the Tcl array.

Let's take a look at an arbitrary example. We will create a function that takes a composite type as an argument and does some calculations based on the attribute values of the type. Let's create our type and the function:

CREATE TABLE orders(orderid int, num_people integer, order_amount decimal);

INSERT INTO orders VALUES(1,1,23);
INSERT INTO orders VALUES(2,3,157);
INSERT INTO orders VALUES(3,5,567.25);
INSERT INTO orders VALUES(4,1,100);

CREATE OR REPLACE FUNCTION tip_calculator(orders, integer) RETURNS decimal
AS $$
  if {$1(order_amount) > 0} {
    set tip [expr (double($1(order_amount) * $2)/100)/$1(num_people)]
    set tip [format "%.2f" $tip]
    return $tip
  }
  return 0;
$$ LANGUAGE...