SAS to PostgreSQL(PADB) code - summing field if they exists

Question

I'm having a challenge with a piece of code from SAS that I need to convert to SQL. Usually I'm very good at this but right not I'm facing a new challenge and so far all my ideas to resolve it are failing and I'm not finding the right way to do so.

I need to be able to pick up field dynamically for this request, like if a field has a certain pattern in it's name I need to sum those fields.

my version of PostgreSQL is 8.0.2, PADB 5.3.3.1 78560

So the table may or may not have a field like bas_txn_03cibc_vcl.

I wrote a function that should output ' ' as bas_txn_03cibc_vcl when the field is not found in the information_schema table and use bas_txn_03cibc_vcl if found.

But when I execute the command I get the error that UDF cannot be used on PADB tables.

"ERROR: XX000: User-defined SQL language function "check_if_field_exists(character varying,character varying,character varying)" cannot be used in a query that references PADB tables."

Right now I'm building a new approach using stored procedure but it will limit the use case. Any other idea on how I can select field dynamically?

Function:

CREATE OR REPLACE FUNCTION check_if_field_exists(_schm text, _tbl text, _field text)
RETURNS text AS
$BODY$
DECLARE 
    _output_ text:=  '' as _field;
BEGIN 
EXECUTE   'SELECT column_name into : _output_ FROM rdwaeprd.information_schema.columns 
where table_schema='''|| _schm||'''   
and table_name='''|| _tbl||'''   
and column_name='''|| _field||'''   
order by table_name,column_name;';  
RETURN _output_;
END
$BODY$
LANGUAGE PLPGSQL;

and then I would use it like this

select indiv_id,ae_psamson.check_if_field_exists('ae_psamson','activ_cc', 'tot_txn_03AMX_AMXE') ,tot_txn_03AMX_AMXD
from activ_cc
group by indiv_id,tot_txn_03AMX_AMXD;

Where the function would either return '' as tot_txn_03AMX_AMXE or simply, tot_txn_03AMX_AMXE.... the idea is to make the query not return an error if the field does not exists.

Like I said I need a new function or approach as this one is not working...

Postgres 8.0 has been [out of support](https://www.postgresql.org/support/versioning/) for 8 years now. You should not use that any more. Plan an upgrade to a supported and maintained version (e.g. 10) **now** — , Aug 29 '18 at 14:34
You could *at least* add the function to your question (the man pages for pg-8-0 are still available) and maybe even explain what PADB-tables are. — wildplasser, Aug 29 '18 at 15:11
PADB tables are standard table created by the current DMBS. The fuction is added in the Q now, but I'm not sure how it will help as the strategy is not working. I need a new strategy, or a function that exist and I'm unaware that will check if a field exists and if not do something else... you know a bit like drop table if exists... — Wired604, Aug 29 '18 at 15:15
You don't need dynamic SQL or plpgsql; you can query the catalogs directly, just like any other table. — wildplasser, Aug 29 '18 at 15:28
I do, look at what this function is suppose to do ... return the field name or return '' as the_field_name.If the field does not exist SQL returns an error and does not compile — Wired604, Aug 29 '18 at 15:40
if found `... and column_name='''|| _field|| ... ` it returns exactly the value of the 3rd argument. (and the `order by` is not necessary: the query can at most return 1 row) — wildplasser, Aug 29 '18 at 15:48
I know that order by is not necessary... I know that it will return my field name if it's found.... thats the idea ... I'm not sure you understand what I'm trying to do here — Wired604, Aug 29 '18 at 15:52
@wildplasser, what I need to do is use all the fields that I find in the information schema as the tables are built differently months after month.... I know the ETL should be standardizing these colomns however I dont have the liberty to change the existing table structure... — Wired604, Aug 29 '18 at 15:53

score 0 · Accepted Answer · answered Sep 04 '18 at 12:28

I managed to make a function that make it work! Basically one of the issue what that information schema was using unsupported function in UDF. This solution works fine:

CREATE OR REPLACE FUNCTION check_if_field_exists(_schm text, _tbl text, _field text)
RETURNS varchar(55)  AS
$BODY$
DECLARE 
    _output_ varchar(55) :=' 0 as '|| _field;
--  name := (SELECT t.name from test_table t where t.id = x);
BEGIN 
EXECUTE  'drop table if exists col_name';
EXECUTE  'create table col_name as SELECT att.attname::character varying(128) AS colname   
FROM pg_class cl, pg_namespace ns, pg_attribute att 
WHERE cl.relnamespace = ns.oid AND cl.oid = att.attrelid AND ns.nspname='''|| _schm ||''' 
and cl.relname='''|| _tbl ||''' 
and colname like '''|| _field||''''; -- INTO _output_;
select colname from col_name into _output_ ;  
if _output_ is null then 
_output_  :=' 0 as '|| _field;
end if;
RETURN _output_  ;
END 
$BODY$
LANGUAGE PLPGSQL;

SAS to PostgreSQL(PADB) code - summing field if they exists

1 Answers1