CODEWITHSUNDEEP
postgresqlpostgresql-8.0
Wired604
Wired604

Reputation: 370

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

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...

Upvotes: 0

Views: 52

Answers (1)

Wired604
Wired604

Reputation: 370

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;

Upvotes: 0

Related Questions