0

In my schema I want to have a primaryID and a SearchID. For every SearchID it is the primaryID plus some text at the start. I need this to look like this:

  • PrimaryID = 1
  • SearchID = Search1

Since the PrimaryID is set to autoincrement, I was hoping I could use a postgresql rule to do the following (pseudo code)

IF PRIMARYID CHANGES
{
SEARCHID = SEARCH(PRIMARYID)
}

This would hopefully occure exactly after the primaryID is updated and happen automatically. So, is this the best way of achieving this and can anyone provide an example of how it is done?

Thank you

Jimmy
  • 12,087
  • 28
  • 102
  • 192
  • 1
    Why do you want to store that value at all if can be derived from the primaryID. Just create a view that returns the "calculated" searchID. Much cleaner in my opinion. But if you think you have to store it, use a trigger instead of a rule. –  Dec 06 '12 at 12:27

1 Answers1

1

Postgres 11 introduced genuine generated columns. See:

For older (or any) versions, you could emulate a "virtual generated column" with a special function. Say your table is named tbl and the serial primary key is named tbl_id:

CREATE FUNCTION search_id(t tbl)
  RETURNS text STABLE LANGUAGE SQL AS
$$
SELECT 'Search' || $1.tbl_id;
$$;

Then you can:

SELECT t.tbl_id, t.search_id FROM tbl t;

Table-qualification in t.search_id is needed in this case. Since search_id is not found as column of table tbl, Postgres looks for a function that takes tbl as argument next.

Effectively, t.search_id is just a syntax variant of search_id(t), but makes usage rather intuitive.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228