Hacker News new | comments | ask | show | jobs | submit login

Another cool thing about Postgres is each table and view you make becomes a type.

Suppose you have a table of fruits:

  create table fruits (
      name text primary key,
      color text references colors,
      season text references seasons
  );
(There are other tables at play, colors and seasons). Anyway, now if you make a function to find you some fruits in season:

  create function get_fruits_in_season(season text)
  returns fruits as
  'select * from fruits where season = $1'
  language sql;
Do you see that I just made a function whose return type is "fruits"? In this example, it's a bit overkill, because all you would really probably want is the name, and so would return "text." But it has been useful for me in real-life examples that are too complicated right now for me to spell out.

Documentation: https://www.postgresql.org/docs/current/static/sql-createfun...




Great tip, minor point, did you mean 'returns setof fruits'? Otherwise your function will only return one row. Opinions vary but i think its another good argument for singular named tables as well: 'returns fruit' and 'returns setof fruit' reads better to my eyes.


Just to emphasize the parent's point about singular vs plural, there are a lot of reasons to go with singular:

* Singular is much friendlier to find/replace, due to the inconsistency of pluralization.

* A good practice is to name association tables by concatenation of the tables being associated (when the association doesn't correspond to a previously named entity). teacher_class is a much better name than teachers_classes, and definitely avoid teacher_classes.

* As you move up the stack, structs, interfaces and classes have strong singular conventions. If you spend much time at higher levels, the habits you develop can lead to little bugs when working deeper in the stack.


Sorry, why yes, you're right!

  create function get_fruits_in_season(season text)
  returns setof fruits as
  'select * from fruits where season = $1'
  language sql;


This is the same as table-valued functions in MS SQL Server, right?




Applications are open for YC Summer 2019

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact

Search: