Use defined data type as the function parameter : Function Parameter « Store Procedure Function « PostgreSQL






  1. PostgreSQL
  2. Store Procedure Function
  3. Function Parameter

Use defined data type as the function parameter

postgres=#
postgres=# CREATE TYPE inventory_item AS (
postgres(#    name            text,
postgres(#    supplier_id     integer,
postgres(#    price           numeric
postgres(# );
CREATE TYPE
postgres=#
postgres=# CREATE TABLE on_hand (
postgres(#    item      inventory_item,
postgres(#    count     integer
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
INSERT 0 1
postgres=#
postgres=# select * from on_hand;
          item          | count
------------------------+-------
 ("fuzzy dice",42,1.99) |  1000
(1 row)

postgres=#
postgres=#
postgres=# CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
postgres-# AS 'SELECT $1.price * $2' LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# select price_extension(ROW('fuzzy dice', 42, 1.99), 1000);
 price_extension
-----------------
         1990.00
(1 row)

postgres=#
postgres=# drop function price_extension(inventory_item, integer);
DROP FUNCTION
postgres=# drop table on_hand;
DROP TABLE
postgres=# drop type inventory_item;
DROP TYPE
postgres=#
postgres=#








Related examples in the same category

1.Here the $1 references the value of the first function argument whenever the function is invoked
2.Pass constant to function
3.OUT parameter
4.Output parameters are most useful when returning multiple values
5.Real number parameter
6.Two out parameters
7.'Anyelement' parameter
8.Pass in a whole row