Computed column in POSTGRESQL-Column Derived from a function

114 Views Asked by At

Want to create a COMPUTED COLUMN in POSTGRESQL which has to be a migration of MSSQLCODE .The computed column is as a result of a function "FUN_GetExternalSystemNameFull"

MSSQL CODE FOR TABLE

   CREATE TABLE ClientApplication(  
ClientApplicationId bigint    IDENTITY(1,1) NOT NULL,   
ClientId bigint NULL,
ExternalSystemNameFull  AS    (dbo.FUN_GetExternalSystemNameFull(ClientApplicationId,' | ')),
Age bigint
)

function FUN_GetExternalSystemNameFull in POSTGRESQL :

CREATE OR REPLACE FUNCTION "FUN_GetExternalSystemNameFull"(v_subscriptionid bigint, v_separator character varying)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
BEGIN
RETURN((SELECT STRING_AGG("c"."ExternalSystemName",coalesce(v_separator,'')) from(select "ExternalSystemName"
      from "ClientApplication"
      where "ApplicationId" = v_subscriptionId) "c"));    
END; $function$

Iam not able to create a POSTGRESQL TABLE with computed column from the function.Please help. Iam using Postgresql 11

0

There are 0 best solutions below