0

I have 2 tables recording company market data.
A table recording stock market symbols

CREATE TABLE appl.symbols (
   id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   symbol VARCHAR(10) NOT NULL,
   exchange VARCHAR(10) NOT NULL,
   date_added DATE NOT NULL DEFAULT CURRENT_DATE,
   active BOOLEAN NOT NULL DEFAULT true,
)

And a table with many columns (about 50) recording details about the company.

CREATE TABLE appl.fundamentals_overview (
    overview_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    symbol_id INT,
    CONSTRAINT fk_symbol
      FOREIGN KEY(symbol_id) 
        REFERENCES appl.symbols(id),
    assettype VARCHAR(255),
    name VARCHAR(255),
    description VARCHAR(1500),
    cik VARCHAR(10),
    currency VARCHAR(10),
    country VARCHAR(10),
    sector VARCHAR(50),
    industry VARCHAR(100),
    address VARCHAR(100),
    fiscalyearend VARCHAR(10),
    latestquarter DATE,
    marketcapitalization BIGINT,
    ebitda BIGINT,
    peratio NUMERIC(20, 4),
    pegratio NUMERIC(20, 4),
    bookvalue NUMERIC(20, 4),
    dividendpershare NUMERIC(20, 4),
    dividendyield NUMERIC(20, 4),
    eps NUMERIC(20, 4),
    revenuepersharettm NUMERIC(20, 4),
    profitmargin NUMERIC(20, 4),
    operatingmarginttm NUMERIC(20, 4)
    ... many more columns
)

I am trying to allow client code to create a row without needing to know unique IDs, and not needing to give a value for all columns. Something like the following needs to be valid (as long as they know the symbol name):

 {"assettype": "Common Stock", 
  "name": "AcmeINC", 
  "cik": "1555752", 
  "currency": "USD", 
  "country": "USA", 
  "fiscalyearend": "December",
  "peratio": 235.56
}

I created a function, but I can not work out how to get the column names, in the right order, and then specify a variable for the column names. This is as far as I got

CREATE OR REPLACE FUNCTION appl.insert_fund_overview(sym TEXT, ex TEXT, js TEXT) RETURNS VOID AS
$func$
DECLARE
    symID BIGINT := 0;
    fullJSON JSON;
    tempJSON JSON;
    colNames TEXT;
BEGIN 
    symID := (SELECT appl.symbols.id FROM appl.symbols WHERE symbol = sym AND exchange = ex);
    tempJSON := json_build_object('symbol_id', symID);
    fullJSON := (js::jsonb) || (tempJSON::jsonb);
    colNames := (SELECT string_agg(elem, ',') FROM json_object_keys(fullJSON) elem);
    --The value of colNames contains the JSON key names, but in wrong order

    -- This does not work
    INSERT INTO appl.fundamentals_overview (colNames)  
    SELECT *  
    FROM json_populate_record(NULL::appl.fundamentals_overview, fullJSON::json);
END
$func$  LANGUAGE plpgsql;
ERROR:  column "colnames" of relation "fundamentals_overview" does not exist
2
  • 1
    you don't actually need to list the columns because json_populate_record() will generate a row with all columns anyway. so you can just leave out the (colnames) part completely
    – user1822
    Commented Jul 10, 2021 at 13:01
  • 1
    Is there an intention behind the mix of json and jsonb (exploiting side effects)? Or just by accident? Please always disclose your version of Postgres. Commented Jul 10, 2021 at 16:53

1 Answer 1

0

As has been commented, you don't have to list target columns for json(b)_populate_record(). While using the same row type, columns are guaranteed to match.

Plus, I would simplify / optimize a bit:

CREATE OR REPLACE FUNCTION appl.insert_fund_overview(_sym text, _ex text, _js jsonb)  -- ②
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sym_id jsonb;
BEGIN
   SELECT INTO _sym_id  to_jsonb(t.*)
   FROM  (SELECT id AS symbol_id FROM appl.symbols
          WHERE symbol = _sym AND exchange = _ex) t;
   
   IF NOT FOUND THEN  -- ③
      RAISE EXCEPTION 'Symbol not found in table appl.symbols!';
   END IF;

-- This works:
   INSERT INTO appl.fundamentals_overview OVERRIDING SYSTEM VALUE  -- ①
   SELECT *
   FROM   jsonb_populate_record(NULL::appl.fundamentals_overview, _js || _sym_id);  -- ②
END
$func$;

① You seem to be providing a value for overview_id, which is an IDENTITY column with GENERATED ALWAYS. You can force input values as demonstrated.
The manual about OVERRIDING SYSTEM VALUE:

If this clause is specified, then any values supplied for identity columns will override the default sequence-generated values.

Alternatively, use OVERRIDING USER VALUE:

If this clause is specified, then any values supplied for identity columns are ignored and the default sequence-generated values are applied.

With OVERRIDING SYSTEM VALUE the underlying SEQUENCE will be out of sync, and you may want to get back in sync. See:

Related:

② Going with jsonb since that allows the simple concatenation jsonb || jsonb (as opposed to json).

to_jsonb(t.*) FROM ( ... ) t is slightly shorter and faster than json_build_object(). See:

③ And I raise an exception if the symbol is not found. You may or may not want that. But if you remove it, you need to special-case _sym_id IS NULL as _js || NULL yields NULL. Like:

_js || COALESCE(_sym_id, '{}')

Or, more verbose but cheaper:

CASE WHEN _sym_id IS NULL THEN _js ELSE _js || _sym_id) END

Alternatively, you might insert a new row into appl.symbols if it does not exist yet. A case of SELECT or INSERT. And you may want that safe under concurrent write load. See:

Related:

4
  • This didn't qute work, I'm back to getting the error ERROR: cannot insert into column "overview_id" DETAIL: Column "overview_id" is an identity column defined as GENERATED ALWAYS.
    – rossco
    Commented Jul 10, 2021 at 22:24
  • This was the reason I went down the path of specifying the column names originally and got stuck. I thought I read examples that showed it was possible, but if It's not then I'm on the wrong track
    – rossco
    Commented Jul 10, 2021 at 22:39
  • @rossco: It is possible to provide target columns (my original answer had an incorrect detail). But there are simpler ways. Either define your IDENTITY column as GENERATED BY DEFAULT instead of GENERATED ALWAYS, or use OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE. See addition above. Commented Jul 10, 2021 at 23:31
  • perfect, thank you!
    – rossco
    Commented Jul 11, 2021 at 2:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.