table value function help

[Glenn Schultz]

Hello,

I have a table value function and would like the first and second input to
take multiple arguments (array or list) I suppose.  Like the below:


create or replace function myfunction(sector, agency, term)
returns table (cusip char(9), sector char(12))
language sql
stable
as $function$

select foo
from
atable
where
sector in (myfunction.sector)

usage

sectorselect('sector_1 sector_2', 'agent_1 agent_2, 120)

Re: table value function help

[Glenn Schultz]

Thanks!

I googled this for 3-days before coming here.  I see what you mean.  Thank
you so much will make the recommended changes.

Glenn

On Thu, Nov 22, 2018 at 11:02 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thursday, November 22, 2018, Glenn Schultz  wrote:
>
>> Hello,
>>
>> I have a table value function and would like the first and second input
>> to take multiple arguments (array or list) I suppose.  Like the below:
>> create or replace function myfunction(sector, agency, term)
>>
>
> Create function myfunction(arg1 text[], arg2 text[]) ...
>
> David J.
>
> p.s. You should avoid using the “char” data type in PostgreSQL, use text
> or varcar instead.
>

Creating a function

[Glenn Schultz]

Hi,

I am trying to create a function to bin based on user value and I am
stuck.  I followed the
postgres create function tutoriall but I am missing something.  Any help
would be appreciated as I think I am just going further off course at this
point

Glenn

CREATE FUNCTION "IncentiveBin"(in Gwac double precision,
 in MtgRaate double precision,
 in BinSize double precision)
  RETURNS double precision
LANGUAGE 'sql'
VOLATILE PARALLEL SAFE
AS
$function$
BEGIN
ceiling((Gwac - MtgRate)/BinSize) * BinSize;
END
$function$

using a function in where

[Glenn Schultz]

All,
I am using the function below to convert a continuous variable to a binned
value.  Sometimes a value other than zero is passed through the query. For
example -.5 result value is passed to the query result.  The basic of the
query is below.

select
incentivebin(wac, rate, .25)
from
my_table
where incentivebin(was, rate, .25) = 0

I have checked the function works correctly and the raw values match those
values expected from simple subtraction and are in the correct bin.  I am
not sure why some values would be allowed through the query. Any ideas
would be appreciated.

Best,
Glenn


CREATE or REPLACE FUNCTION embs_owner.IncentiveBin(IN "Gwac" double
precision,
  IN "MtgRate" double precision,
  IN "BinSize" double precision)
RETURNS double precision
LANGUAGE 'sql'
PARALLEL SAFE
AS 'select ceiling(($1 - $2)/$3) *$3';

ALTER FUNCTION embs_owner.IncentiveBin(double precision, double precision,
double precision)
OWNER TO embs_owner;

Amazon Aurora

[Glenn Schultz]

I have a Postgres database of about 1.5 terabytes on amazon aurora.  It runs 
super slow.  Has anyone experienced this and if so how was the problem 
addressed?
Glenn
Sent from my iPhone

initialize and use variable in query

[Glenn Schultz]

All,

I need to initialize a variable and then use it in query.  Ultimately this
will part of a recursive CTE but for now I just need to work this out.  I
followed the docs and thought I needed something like this.  But does not
work-maybe I have misunderstood.  Is this possible?

SET max_parallel_workers_per_gather = 8;
SET random_page_cost = 1;
SET enable_partitionwise_aggregate = on;
Do $$
Declare startdate date;
BEGIN
startdate := (select max(fctrdt) from fnmloan);
END $$;
select
fnmloan.loanseqnum
,fnmloan.currrpb
from
fnmloan
join
fnmloan_data
on
fnmloan_data.loanseqnum = fnmloan.loanseqnum
where
fnmloan.fctrdt = (select * from startdate)

limit 10

Re: initialize and use variable in query

[Glenn Schultz]

Thanks!

On Sat, Dec 29, 2018 at 10:06 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Saturday, December 29, 2018, Ray O'Donnell  wrote:
>>
>> A couple of things off the top of my head:
>
>
> Sorry but, no.
>
>>
>> (i) I think you need "language plpgsql" (or whatever) after the DO block.
>
>
> As the docs state plpgsql is the default for a DO block lacking a language
> specifier.
>
>
>> (ii) That assignment in the DO should probably be:
>>
>>   select max(fctrdt) into startdate from fnmloan;
>>
>
> The original form is perfectly valid plpgsql;
>
> The DO block worked just fine.  It’s just that everything it did was
> discarded at the end of it because nothing that permanently affected the
> parent SQL session happened.
>
> David J.
>
>

Recursive CTE

[Glenn Schultz]

All,
Following my earlier post on variable instantiation, I rethought how I was
working with dates and realized I can fix the date and use static
interval.  I came up with this recursive CTE which is the end goal.
However, the problem is that the convexity query cannot be used as a
subquery.  So I think I need to use a join of convexity on the original
query - not sure I am little stuck at this point but I feel I am close.
Any help would be appreciated.

-Glenn

SET max_parallel_workers_per_gather = 8;
SET random_page_cost = 1;
SET enable_partitionwise_aggregate = on;
with recursive convexity (fctrdt, CPR3mo) as
(
select
cast((select max(fctrdt) - interval '1 month' from fnmloan) as date) as
"fctrdt"
,round(
smmtocpr(
cast(
sum(currrpb * case when fnmloan.fctrdt = fnmloan_data.fctrdt then
fnmloan_data.event else 0 end)/ sum(currrpb)
as numeric) * 100
),4) * 100 as "CPR 3mo"
from
fnmloan
join
fnmloan_data
on
fnmloan_data.loanseqnum = fnmloan.loanseqnum
where
fnmloan.fctrdt between ((select max(fctrdt)-interval '1 month' from
fnmloan) - interval '2 month') and (select max(fctrdt) - interval '1 month'
from fnmloan)
and
fnmloan.poolprefix = 'CL'
union all
select
cast((select max(fctrdt) - interval '1 month' from convexity) as date) as
"fctrdt"
,round(
smmtocpr(
cast(
sum(currrpb * case when fnmloan.fctrdt = fnmloan_data.fctrdt then
fnmloan_data.event else 0 end)/ sum(currrpb)
as numeric) * 100
),4) * 100 as "CPR 3mo"
from
fnmloan
join
fnmloan_data
on
fnmloan_data.loanseqnum = fnmloan.loanseqnum
where
fnmloan.fctrdt between ((select max(fctrdt)-interval '1 month' from
convexity) - interval '2 month') and (select max(fctrdt) - interval '1
month' from convexity)
and
fnmloan.poolprefix = 'CL'
and
convexity.fctrdt <= (select max(fctrdt) - interval' 12 months' from fnmloan)
)
select * from convexity

query with regular expression

[Glenn Schultz]

All,

I am writing a query to select * from where ~ '[regx]  an example of the
sting that I am matching is below

FHLG16725

The first two alpha characters to match are FN, FH, GN any alpha characters
between those and the numeric don't matter as the first two alpha + numeric
will create a unique.

reading the docs I am pretty sure I need to use ~ for bracket expression

I tried '^[FN-FG-GN][0-9]' but does not seem to work.  I have to admit I am
weak on regex - never quite seem to be able to get it through my coconut.

Any help would be appreciated,
Glenn

Re: query with regular expression

[Glenn Schultz]

Thanks for the tip!

On Thu, Jan 3, 2019 at 12:58 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thursday, January 3, 2019, Glenn Schultz  wrote:
>
>> All,
>>
>> I am writing a query to select * from where ~ '[regx]  an example of the
>> sting that I am matching is below
>>
>> FHLG16725
>>
>> The first two alpha characters to match are FN, FH, GN any alpha
>> characters between those and the numeric don't matter as the first two
>> alpha + numeric will create a unique.
>>
>> reading the docs I am pretty sure I need to use ~ for bracket expression
>>
>> I tried '^[FN-FG-GN][0-9]' but does not seem to work.  I have to admit I
>> am weak on regex - never quite seem to be able to get it through my coconut.
>>
>
> Yeah...that’s not even close...not exactly sure what it would match but
> it’s only two characters, one letter maybe and one number.  You seem to
> want capturing groups though so using the ~ operator isn’t going to work,
> you need to use the function.
>
>
>>
>> Any help would be appreciated,
>> Glenn
>>
>
> ~ ‘^(FN|FH|GN)[A-Z]*[0-9]+$’
>
> David J.
>

Help with insert query

[Glenn Schultz]

All,

The query below is designed to insert into a table.  This works when I have
a single loan which I insert.  However, if remove the part of the where
clause of a single loan the insert does not work.  The table fnmloan is a
large table with 500mm + rows and the query runs for about 4 hours.  Any
idea of how to get this to work?  I am a little stumped since the query
works with one loan.

Glenn

SET max_parallel_workers_per_gather = 8;
SET random_page_cost = 1;

truncate fnmloan_balance;
insert into fnmloan_balance (
fctrdt, loanseqnum, secmnem, beginbal, scheduled,endbal,smm
)

select
fnmloan.fctrdt
,fnmloan.loanseqnum
,fnmloan.secmnem
--,fnmloan.orignoterate
--,fnmloan.loanage
--,fnmloan.origloanamt
,fnmloan.currrpb as beginbal
,round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
  cast(fnmloan.remterm - 1 as numeric),
  cast(fnmloan.currrpb as numeric)),4)) as scheduled
,coalesce(endbal.currrpb,0) as endbal
,abs(round(
  cast((fnmloan.currrpb - coalesce(endbal.currrpb,0) -
round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
  cast(fnmloan.remterm - 1 as numeric),
  cast(fnmloan.currrpb as numeric)),4)) )/(fnmloan.currrpb -
round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
  cast(fnmloan.remterm - 1 as numeric),
  cast(fnmloan.currrpb as numeric)),4)) ) as numeric)
  ,4)) as SMM

from
(
 select * from fnmloan
 where
 fctrdt < '03-01-2019'
 and
 loanseqnum = '5991017042'
) as fnmloan


left outer join
(select
fctrdt - interval '1 month' as fctrdt
,loanseqnum
,orignoterate
,loanage
,origloanamt
,currrpb
from fnmloan
) as endbal

on fnmloan.loanseqnum = endbal.loanseqnum
and fnmloan.fctrdt = endbal.fctrdt

iterate over partitions

[Glenn Schultz]

Hi All,

I have a large table partioned by month.  I would like to run a query -
which adds derived data to the current data and inserts the data into a new
table.  The new table is the target for users.

How can I iterate over the partition tables to insert data and build the
new table?
This is done monthly, am I better off creating a materalized view each
month?

Best,
Glenn