PostgreSQL/Select Query/Row

Cast row function

   <source lang="sql">

postgres=# postgres=# CREATE FUNCTION getf1(mytable) RETURNS int postgres-# AS "SELECT $1.f1" postgres-# LANGUAGE SQL; CREATE FUNCTION postgres=# postgres=# CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric); CREATE TYPE postgres=# postgres=# CREATE FUNCTION getf1(myrowtype) RETURNS int postgres-# AS "SELECT $1.f1" postgres-# LANGUAGE SQL; CREATE FUNCTION postgres=# postgres=# SELECT getf1(ROW(1,2.5,"this is a test")::mytable);

getf1

---

    1

(1 row) postgres=# postgres=# SELECT getf1(CAST(ROW(11,"this is a test",2.5) AS myrowtype));

getf1

---

   11

(1 row) postgres=# postgres=# drop function getf1(myrowtype); DROP FUNCTION postgres=# drop type myrowtype; DROP TYPE postgres=# drop table mytable cascade; NOTICE: drop cascades to function getf1(mytable) DROP TABLE postgres=# postgres=#

      </source>
   
  

Pass "row" function to function

   <source lang="sql">

postgres=# postgres=# postgres=# CREATE TABLE mytable(f1 int, f2 float, f3 text); CREATE TABLE postgres=# postgres=# CREATE FUNCTION getf1(mytable) RETURNS int AS "SELECT $1.f1" LANGUAGE SQL; CREATE FUNCTION postgres=# postgres=# -- No cast needed since only one getf1() exists postgres=# SELECT getf1(ROW(1,2.5,"this is a test"));

getf1

---

    1

(1 row) postgres=# postgres=# postgres=# drop function getf1(mytable); DROP FUNCTION postgres=# drop table mytable; DROP TABLE postgres=# postgres=# postgres=#

      </source>
   
  

Row Constructors

   <source lang="sql">

postgres=# -- Row Constructors postgres=# postgres=# SELECT ROW(1,2.5,"this is a test");

          row

---

(1,2.5,"this is a test")

(1 row) postgres=# postgres=#

      </source>
   
  

Using "Row" function in a function

   <source lang="sql">

postgres=# postgres=# CREATE TABLE emp ( postgres(# name text, postgres(# salary numeric, postgres(# age integer, postgres(# cubicle point postgres(# ); CREATE TABLE postgres=# postgres=# insert into emp values ("None", 1000.0, 25, "(2,2)"); INSERT 0 1 postgres=# postgres=# postgres=# CREATE FUNCTION new_emp() RETURNS emp AS $$ postgres$# SELECT ROW("None", 1000.0, 25, "(2,2)")::emp; postgres$# $$ LANGUAGE SQL; CREATE FUNCTION postgres=# postgres=# SELECT (new_emp()).name; REATE

name

---

None

(1 row) postgres=# postgres=# drop function new_emp(); DROP FUNCTION postgres=# drop table emp; DROP TABLE postgres=# postgres=#

      </source>