Advertisement
rockdrilla

PL/PgSQL 'RECORD' type gotchas

Aug 18th, 2017
441
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 0.68 KB | None | 0 0
  1. CREATE OR REPLACE FUNCTION wtf1(TEXT) RETURNS RECORD
  2. LANGUAGE plpgsql AS $$
  3.   DECLARE
  4.     r RECORD;
  5.   BEGIN
  6.     SELECT 1::INTEGER AS "a1", 2::TEXT AS "b2" INTO r;
  7.     RETURN r;
  8.   END;
  9.   $$;
  10.  
  11. -- # SELECT wtf1('z');
  12. --  wtf1
  13. -- -------
  14. --  (1,2)
  15. -- (1 row)
  16.  
  17. -- # SELECT "a1" FROM wtf1('z');
  18. -- ERROR:  a column definition list is required for functions returning "record"
  19. -- LINE 1: SELECT "a1" FROM wtf1('z');
  20. --                          ^
  21.  
  22. CREATE OR REPLACE FUNCTION wtf2(TEXT) RETURNS TEXT
  23. LANGUAGE plpgsql AS $$
  24.   DECLARE
  25.     r RECORD;
  26.   BEGIN
  27.     r := wtf1($1);
  28.     RETURN r.a1::TEXT;
  29.   END;
  30.   $$;
  31.  
  32. -- # SELECT wtf2('z');
  33. --  wtf2
  34. -- ------
  35. --  1
  36. -- (1 row)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement