Computed/generated/stored columns
I have a Postgres table which should be migrated to IRIS. The table has a computed column, like:
CREATE TABLE example_table (
id VARCHAR(10) PRIMARY KEY,
normalized_id VARCHAR(10) GENERATED ALWAYS AS (LPAD(id, 10, '0')) STORED
);
IRIS also has the LPAD function but I can't figure out how to achieve the same result using pure SQL DDL.
Tried this:
CREATE TABLE example_table (
id VARCHAR(10) PRIMARY KEY,
normalized_id VARCHAR(10) COMPUTECODE {
set {normalized_id} = LPAD({id}, 10, '0')
}
COMPUTEONCHANGE(id))
Got the following error:
SQL Error [400] [S1000]: [SQLCODE: <-400>:<Fatal error occurred>]
[Location: <ServerLoop>]
[%msg: <ERROR #5475: Error compiling routine: SQLUser.exampletable.1. Errors: ERROR: SQLUser.exampletable.1.int(1003) #1017: Invalid operator : ''0')' : Offset:31 [znormalizedidCompute+3^SQLUser.exampletable.1]
> ERROR #5030: An error occurred while compiling class 'SQLUser.exampletable'>]
Changing single quotes to double quotes doesn't work
Comments
To address the implementation of a computed column in InterSystems IRIS similar to the PostgreSQL query you provided:
IRIS does not support GENERATED ALWAYS AS directly in SQL DDL commands for computed columns. The approach involves using COMPUTECODE. However, the error you encountered, suggests issues with syntax or implementation. A viable alternative might involve defining the logic in an appropriate trigger function or querying with derived columns directly [1][2].
For further assistance, you might consider asking for specific guidance on the Developer Community, especially if the proposed methods or adaptations still do not align with your requirements.
Sources:
LPAD is a SQL function, not an ObjectScript function, which is why it didn't work for you if you replaced the single quotes with double quotes.
USER>write ">"_LPAD(1,10,"0")_"<" >< USER>write ">"_$$lpad^%qarfunc(1,10,"0")_"<" >0000000001<
There are two ways to solve the problem: using embedded SQL or an analog of LPAD for ObjectScript, for example:
CREATE TABLE example_table (
id VARCHAR(10) PRIMARY KEY,
normalized_id VARCHAR(10) COMPUTECODE {
&sql(select LPAD(:{id}, 10, '0') into :{*})
}
COMPUTEONCHANGE(id))Thanks Vitaly, it looks like a magic spell but it works.
I'm curious why LPAD, RPAD and similar string functions are not exposed as $SYSTEM.SQL.Functions class members
I'm curious why LPAD, RPAD and similar string functions are not exposed as $SYSTEM.SQL.Functions class membersThis is a good question that I would also like to get an answer to.
Probably because extending ObjectScript Functions in %LANGF00.mac is so simple for ages
ZLPAD(%txt,%len,%pad) quit$$lpad^%qarfunc(%txt,%len,%pad)
ZRPAD(%txt,%len,%pad) quit$$rpad^%qarfunc(%txt,%len,%pad)
and then
USER>s txt="robert"
USER>w$zrpad(txt,20,"*")
robert**************
USER>w$zlpad(txt,20,"*")
**************robertRather, the discussion was about out-of-the-box equivalents of some SQL functions, so that programmers wouldn't have to reinvent the wheel. Furthermore, the routine of ^%qarfunc is undocumented.
For example, making an Object Script analog of VECTOR_COSINE for integer vectors yourself is a non-trivial task, given that $VECTOROP doesn't support the integer type.
Sometimes it's easier to use embedded SQL.
I'm pleased!
The old Caché code for embedded SQL is still around !
And is working !!
Thanks to @Vitaliy Serdtsev