[GENERAL] psql question

[Little, Douglas]

I'm looking for a way where I can tailor DDL scripts for deployment with 
environment variables.
Support I have a requirement to prefix table names with dev_ , fqa_, or prod_

I'd like to have a file for each env with their own unique settings - host, 
dbname
Dev.sql
\set env dev
Fqa
\set env fqa

prod
\set env prod

and then

my deployment script would have
ddl.sql
\i :env.sql
Create table schema.:env_tablename


I tried it and didn't work.
p1gp1=> \set env dev
p1gp1=> \i P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql
P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql: No such file or directory

Any thoughts on how I might get this to work?

Thanks




Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CDFF01.6452A380]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [GENERAL] psql question

[Little, Douglas]

Thanks to steve and Al for the suggestions.

I did get the variable  concatenation solution to work.
\set env `echo $TARGETSERVER`
\echo env :env
\set envfile 'P1GP1_ETL_STAGE_TBLS_BIO6113_100.':env'.sql'
\echo envfile :envfile
  -- envfile P1GP1_ETL_STAGE_TBLS_BIO6113_100.DEV.sql

However it seems that I can't use the psql variables within sql.
Without quotes the variable name is used in the sql and the table is created, 
but since it's created with the variable name and not value it won't execute.
With quotes,  the variable  gets resolved but the syntax is invalid.  The 
syntax requires that the  file location be in quotes.

I'm using greenplum's external table feature and the external filename needs to 
change by environment.

Create external table  (
Col_a 
)
LOCATION (
'gphdfs://':filepath'/DimSiteVisit/part*'
)


Runtime log
ERROR:  syntax error at or near ":"
LINE 44: 'gphdfs://':filepath'/DimSiteVisit/part*'

I think I'm going to shift down to using shell features.


Thanks again for the help.

From: Steve Crawford [mailto:scrawf...@pinpointresearch.com]
Sent: Wednesday, January 30, 2013 4:49 PM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-general@postgresql.org)
Subject: Re: [GENERAL] psql question

On 01/30/2013 01:51 PM, Little, Douglas wrote:
I'm looking for a way where I can tailor DDL scripts for deployment with 
environment variables.
Support I have a requirement to prefix table names with dev_ , fqa_, or prod_

I'd like to have a file for each env with their own unique settings - host, 
dbname
Dev.sql
\set env dev
Fqa
\set env fqa

prod
\set env prod

and then

my deployment script would have
ddl.sql
\i :env.sql
Create table schema.:env_tablename


I tried it and didn't work.
p1gp1=> \set env dev
p1gp1=> \i P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql
P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql: No such file or directory

Any thoughts on how I might get this to work?

Perhaps try concatenating variables then executing the result. For example, 
given a file "foo.psql" containing "select now();" and "bar.psql" containing 
"select 'Hello world';"

steve@[local] => \set env foo
steve@[local] => \set scriptname :env '.psql'
steve@[local] => \i :scriptname
  now
---
 2013-01-30 14:45:36.423836-08

steve@[local] => \set env bar
steve@[local] => \set scriptname :env '.psql'
steve@[local] => \i :scriptname
  ?column?
-
 Hello world

Cheers,
Steve

[GENERAL] Reverse Engr into erwin

[Little, Douglas]

Thanks in advance for thinking about my problem.

As I suspect you know,  CA Erwin doesn't support Postgres or greenplum.
But they do support ODBC for reverse engineering.

When I reverse, Erwin executes the standard ODBC metadata queries for the 
system catalog.
The process works fine, but I'm finding that the datatype that's returned is 
the internal PG type instead of the standard set of ANSI types.
Eg,   a BIGINT column returns as INT8.

I could live with the non-standard lables, if the resulting DDL would execute.  
But PGadmin doesn't know the data types.

I've traced the same function to PGadmin and find that it is submitting a query 
with the format_type(type oid) function to convert to ANSI types.

I've installed the newest PG ODBC driver and the problem remains.

I'm hoping that there is a setting in ODBC that I can use to have the ANSI 
datatypes returned.
Short of that,  Is there anyway to change the Query that's submitted

It's querying the pg_attribute, pg_type type with the schema name specified 
Pg_catalog,  so I don't think I can over ride the select. Perhaps a rule might 
work.

Any suggestions on moving forward?

Thanks



Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CE02F5.99F2EAC0]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] design question - repeated updates on temp or perm table.

[Little, Douglas]

Hi,

Design question.
Does it make a difference for a function to repeatedly update a temp table 
verses the permanent table?

We are working in a data warehousing environment.
We have daily etl that's used to update our dimension table which has approx. 
500k rows.
A dimension row holds all of the descriptive attributes related to any number 
of transaction records.

The dimension table has about 50 attributes sourced from various lookup tables.
In the dimension table we have the id and the name/description.
If any of the lookup tables are updated, we need to propagate the new 
description to all dimension rows that use that value.

Instead of a cursor going thru all of the rows and updating the columns,  we 
use a column update approach where we update specific columns for all rows that 
need a update.

So the function has about 50 update statements,  each setting 1 column at a 
time.
Individual update statements update between a few thousand to 300k rows.

We're refactoring the code now and considering updating a temp table repeatedly 
and finally updating the perm table, instead of updating the perm table 50 
times.

I don't see much difference between a temp and perm table.  I suspect that the 
update process is the same for both types.
Can anybody comment if there'd be any difference in update performance?
Would it be worth the effort to vacuum after each update?

Note - any of the rows in the perm table may need to be updated,  so the temp 
table would be a copy of the perm table.

Thanks in advance.


Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CE0914.85604D80]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] FW: statement failure

[Little, Douglas]

Hi,

I have a dev and prod Greenplum system (4.2.2.4) that is based on PG 8.2.15
I have a function that's been working fine on dev, but when implemented in prod 
had a syntax error.
I was easily able to fix by casting, but I can't see why the message didn't 
show up on our dev machine.

I've reviewed the guk settings and can't find anything relevant.

Any ideas what might be different and causing problems.
Thanks in advance



ERROR:  column "logtransaction" is of type integer but expression is of type 
text



LINE 1: ...abase,loguser,loghost,logsession, logsessiontime, logtransac...

 ^

HINT:  You will need to rewrite or cast the expression.

QUERY:  insert into dba_work.table_usage_log(logtime, 
logdatabase,loguser,loghost,logsession, logsessiontime, logtransaction, 
logseverity, logstate, logdebug) select logtime, 
logdatabase,loguser,loghost,logsession, logsessiontime, logtransaction, 
logseverity, logstate, logdebug from dba_work.gp_log_master_ext where 
logmessage ilike '%select%' and logmessage ilike '%from%' and logmessage not 
ILIKE 'execute%' and logmessage not ILIKE '%gp_%' and logmessage not ILIKE 
'%pg_%' and loguser not in ('gpadmin','gp_php_read','gpmon','ods','dlittle') 
and date_trunc('day',logtime) =  $1

CONTEXT:  PL/pgSQL function "table_usage_analyzer" line 31 at SQL statement



Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CE1985.EBEAC720]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] table dump function

[Little, Douglas]

I need a pl/pgsql function that will dump a table ddl  so I can export the 
definition to a file  before I drop the object.

the psql \d command won't work, since it doesn't dump the table ddl, it just 
lists the table's attributes and indexes.

I've got the function mostly working,  but need help translating the ACL list 
into the grant commands.

Are there any standard PG functions that would be useful  for creating the 
grants?

Thanks in advance.


Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CE1B1A.4F4C8E80]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [GENERAL] FW: statement failure

[Little, Douglas]

both dev and prod are 8.2.15
Version stringPostgreSQL 8.2.15 (Greenplum Database 4.2.2.4 build 1) on 
x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Oct  3 
2012 20:28:56

can you tell me what change in 8.3  maybe Greenplum incorporated some of the 
changes in their build.

Thanks

From: Merlin Moncure [mailto:mmonc...@gmail.com]
Sent: Wednesday, March 06, 2013 9:47 AM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-general@postgresql.org)
Subject: Re: [GENERAL] FW: statement failure

On Wed, Mar 6, 2013 at 9:38 AM, Little, Douglas 
mailto:douglas.lit...@orbitz.com>> wrote:
Hi,

I have a dev and prod Greenplum system (4.2.2.4) that is based on PG 8.2.15
I have a function that's been working fine on dev, but when implemented in prod 
had a syntax error.
I was easily able to fix by casting, but I can't see why the message didn't 
show up on our dev machine.

can you double check pg version on both dev and prod.  My guess is that prod is 
8.3+ -- casting rules were tightened with 8.3.

merlin

Re: [GENERAL] table dump function

[Little, Douglas]

Thanks for the suggestion Adrian, 

Unfortunately,  my process is executing from psql,  so to start pg_dump from 
within psql is a bit challenging.  
We have 1 instance where we start an OS process from a function, but it's new 
territory for us.

My function is dumping everything but the ACL grant list.   I currently have a 
function that will  build and execute the grants from the acl list, 
so it's just a simple exercise to get it to dump the statements. 

I was just  hoping that there were standard functions for doing this. 
Thanks
doug



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] table dump function

[Little, Douglas]

Adrian,
Thank you for your comments. 
This is part of our deployment framework.   DDL files are submitted to the 
framework, which eventually run as psql scripts. 

thanks for the tip about pg_dump.c  I'll take a look. 
I'll also look at escaping out from psql.   

Thanks


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@gmail.com] 
Sent: Thursday, March 07, 2013 12:19 PM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-general@postgresql.org)
Subject: Re: [GENERAL] table dump function

On 03/07/2013 08:42 AM, Little, Douglas wrote:
> Thanks for the suggestion Adrian,
>
> Unfortunately,  my process is executing from psql,  so to start pg_dump from 
> within psql is a bit challenging.
> We have 1 instance where we start an OS process from a function, but it's new 
> territory for us.

Yeah, my suggestion was offered in the spirit of not reinventing the
wheel:) When you say you are running from inside psql, do you mean 
interactively or from script? As Rob mentioned from psql you can get to the 
shell by doing \!.

>
> My function is dumping everything but the ACL grant list.   I currently have 
> a function that will  build and execute the grants from the acl list,
> so it's just a simple exercise to get it to dump the statements.
>
> I was just  hoping that there were standard functions for doing this.

Not that I know of. If you feel adventurous you could look at dumpTableSchema 
in pg_dump.c in the source to see how pg_dump does it.

> Thanks
> doug
>


--
Adrian Klaver
adrian.kla...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] object dependency workaround

[Little, Douglas]

Hello,
We're migrating to Greenplum, and in our currently portfolio we base everything 
on views.
In our prior system, we could alter tables and then refresh the views, but in 
PG/greenplum most alters are blocked because of the dependent objects.

I understand that I can walk the pg_depends table to see the linkages, but what 
I really want to know
Is there a way to change the system behavior so that  ALTERs are allowed, 
perhaps invalidating dependent objects

Short of this, is there a recommended framework/functions for spooling off the 
dependent objects, performing the alter and then recreating all of the 
dependent objects?

Thanks  in advance.
Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
douglas.lit...@orbitz.com

 [cid:image002.jpg@01CA459B.F991A680]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [GENERAL] Right Join Question

[Little, Douglas]

Andrew, 
I think you want a full outer join.   If I understood correctly, you want all 
real data, plus all codes without data. 
Doug


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Scott Marlowe
Sent: Thursday, October 22, 2009 6:07 PM
To: Bierbryer, Andrew
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Right Join Question

On Thu, Oct 22, 2009 at 12:43 PM, Bierbryer, Andrew
 wrote:
> I am trying to solve the following problem. I have a certain column,
> to_state, that can take a finite set of values. I want to make sure that my
> query always returns those sets of finite values, even if not all of the
> values are represented in the results. In essence, I am looking to expand
> the result returned by the query by using a right join.
>
> When I do the following simple query, this seems to work.
>
> create table hat (the_hat integer);
> insert into hat values (1),(2),(3);
> create table cat (the_hat integer);
> insert into cat values (2),(3),(4),(5);
>
> select the_hat from hat
> right join cat c using(the_hat)
>
> The right join changes the results query from 1,2,3 to 2,3,4,5.

A right join will return all the rows on the right that aren't removed
by the where clause, and add the rows on the left that match, and
return nulls for those values where there is no match.

Perhaps you want a left join here?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pgadmin support for writing files or psql \copy command

[Little, Douglas]

From: Little, Douglas
Sent: Tuesday, October 27, 2009 4:31 PM
To: 'pgsql-gene...@postgresql.org.'
Subject: Pgadmin support for writing files or psql \copy command

Hi,

I have a script I'd like to submit from pgadmin that needs to export query 
results to a file.
PSQL supports this need thru \copy command
Is there anyway I can incorporate into my pgscript script or accomplish some 
other way?
I reviewed the pgscript doc and didn't find anything there either.

Thanks
Doug




Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com<mailto:douglas.lit...@orbitz.com>

 [cid:image001.jpg@01CA5722.F62CB4B0]   orbitz.com<http://www.orbitz.com/> | 
ebookers.com<http://www.ebookers.com/> | 
hotelclub.com<http://www.hotelclub.com/> | 
cheaptickets.com<http://www.cheaptickets.com/> | 
ratestogo.com<http://www.ratestogo.com/> | 
asiahotels.com<http://www.asiahotels.com/>

<>

[GENERAL] String library

[Little, Douglas]

I'm working a string parser.  Is there a user module that expands the set of 
string functions?



Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CC1C89.1BA16100]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] problems viewing information_schema.schemata

[Little, Douglas]

Hi,
Some of my users are using sqldbx  and it's not working due to a dependence on 
displaying the content of information_schema.schemata
When I query the view I see content.  When they do it, nothing - 0 rows - from 
sqldbx, pgadmin or other client.

I suspect that they don't have access to a catalog table,  but can't find it.
What is the definition for schemata?

Thanks


Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CC4C53.AE4FBE80]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] describe command for

[Little, Douglas]

Is there a postgres sql command/function that will display an object ddl?

I know of the psql metacharacters, but don't know how I'd submit from a 
non-psql client like sqldbx.
I see that pgadmin generates a query and then formats into a ddl statement,  
but this seems like a lot of work for something that I would expect to be part 
of the command set.

Thanks


Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CD4583.580D4770]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] composite type use in pl/gpsql

[Little, Douglas]

Hello,
Thanks in advance for taking my question.
Running on Greenplum 4.1.2/ based on PG 8.2.3 kernal

We make extensive use of functions to do our ETL.
So, I'm building a stored procedure template for our developers.
I'd like the template to log the sql statements to a logging table for 
audit/debug purposes.  Statements are logged after execution so we have a 
completion code.
I'd also like to have an exception block for each statement - so prior steps 
get committed.

Since out procedures can have a large number of steps  <20,  I'd like to avoid 
repeating the exception and logging code.
In PL/pgsql  there doesn't seem to be a subroutine/goto concept,  so I am 
trying to make the statements as concise as possible utilize composite types 
for the log table


-- template
-- header
-- blah
-- change log
-- blah
-- declare
Logsp type_log_site_process%ROWTYPE;
BEGIN
-- function setup
logsp.proc_id:=0;
logsp.proc_name  :=vSpName;
logsp.step_id:=1;
logsp.step_desc  :='';
Logsp.step_starttime := clock_timestamp();
Logsp.step_endtime   := clock_timestamp();
logsp.step_returncode :='';
logsp.activity_count :=1;
logsp.status_desc:='';
logsp.status_flag:='P';

-- step block

-- setup
Starttime = clock_timestamp();

-- execute sql
-- exception block
   End time = clock_timestamp();

-- log statement

Instead of
INSERT INTO log_site_process(
id, proc_id, proc_name, step_id, step_desc, step_starttime, 
step_endtime,
step_returncode, activity_count, status_flag, status_desc)
VALUES (logsp.proc_id
   , logsp.proc_name
   , logsp.step_id
   , logsp.step_desc
   , Logsp.step_starttime
   , Logsp.step_endtime
   , logsp.step_returncode
   , logsp.activity_count
   , logsp.status_desc
   , logsp.status_flag
;
I'd like
insert into log_site_process select * from (Logsp);   -- or values (logsp)

but I can't seem to get it to work.

ERROR:  syntax error at or near "$1"
LINE 1: ...ert into oww_mart_tbls.log_site_process select * from ( $1 )
   ^
Can someone let me know if I can do this and what the syntax is.


Thanks
Doug


Doug Little

[GENERAL] composite type use in pl/gpsql

[Little, Douglas]

Merlin writes"
first, the way to do insert from composite type is like this:

insert into foo select (f).*;

if f is type of foo.  The actual error you're getting is probably"


I gave this a try.  Still bumping into syntax errors
CREATE TYPE oww_mart_tbls.type_log_site_process AS
   (proc_id integer,
proc_name character varying(500),
step_id integer,
step_desc character varying(2000),
step_starttime timestamp without time zone,
step_endtime timestamp without time zone,
step_returncode character varying(500),
activity_count integer,
status_flag character varying(1),
status_desc character varying(2000));
ALTER TYPE oww_mart_tbls.type_log_site_process
  OWNER TO gpadmin;

Create function ...
DECLARE
-- standard variables
vSpReturn_code text;
Logsp oww_mart_tbls.type_log_site_process%ROWTYPE;
.

insert into oww_mart_tbls.log_site_process(proc_id, proc_name, step_id, 
step_desc, step_starttime, step_endtime, step_returncode, activity_count, 
status_flag, status_desc)
 select (logsp).*;

works.
Thanks for the help.


Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CD497A.68F0C620]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] error handling

[Little, Douglas]

Hello,

Greenplum 4.1.2.4 (PG 8.2.3)
We are revising how we implement functions in order to better capture and 
handle fatal errors.

What we want to have happen,

1.   is to have the fatal error captured,

2.   logged to our processing table,

3.   then have the function & psql exit with a non-zero return code, 
informing Informatica of the process failure.

I'm having several problems.

1.   Under GP,  any function called from psql is committed as a single 
transaction.   I can handle the sql exception, and log the error to the table,  
and return a non-zero return code to psql.

2.   I don't know how to raise exception in the psql script.  All I can 
imagine is to nest the function calls,  having the outer function issue raise 
exception - but then the entire transaction is rolled back since the 
transactions are nested.  so I loose the logging messages.

3.   I'd like to have psql capture the return value and provide to a 2nd 
function which would then raise exeception.   But don't know and can't tell 
from the doc if I can assign function output to psql metavariables.

4.   Don't know how to do anything with the psql metavariable   ieif 
:last_return_code >= 16 then raise exception.


Suggestions would be appreciated.
Thanks



Doug Little

[GENERAL] log select access

[Little, Douglas]

Hello everybody,

For PCI compliance I need to log user access to my PCI columns in a table and 
retain for 2 years.
I know I can grep the log,  but with 1m log rows/day and infrequent PCI access, 
 I'm thinking this isn't the most efficient method.

I've been thinking about a SELECT rule, for the access views defined on the 
tables with PCI columns.
I reviewed the doc but belive I'm constrained
I would like to create a select rule that would log the statement in addition 
to executing the select.

"Presently, ON SELECT rules must be unconditional INSTEAD rules and must have 
actions that consist of a
single SELECT command. Thus, an ON SELECT rule effectively turns the table into 
a view, whose visible"

makes me think I can't do this.   Any advice how I might accomplish the goal.

format
CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

Create rule pci_select as on select to creditcard do
Instead (begin
Insert into pci_log( sql statement);
Select * from creditcard;
end)


Thanks

Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CD6B4B.4CEC3480]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] conversion from epoch

[Little, Douglas]

I have an input source that is in epoch time.Is there a built-in conversion 
to changing to timestamp

Thanks


Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CD6E36.D9E7D130]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] Function to dump function ddl

[Little, Douglas]

Hi,
In deploying new versions of function source, I want to export the current 
definition to a file.
After looking around it seems that I needed to create my own function.

I got the function to work, but am having a slight problem with the execution 
of the exported file.
It seems that if I include line breaks they are not being interpreted by psql 
on input.

Other than leaving the line breaks out,  does anybody have any suggestions for 
getting psql to ignore the line feeds.
I tried to replace the \n with blanks on output, but failed.

Some specifics might help.
Running on redhat,  pg 8.2.15/greenplum 4.1.2.6

Ddl export
d1gp1=> \copy (SELECT  
dba_work.pg_get_functiondef('da_test.aggairbookingitinasbookedprc'::regproc)) 
to test1.sql
d1gp1=>

exported file
[dlittle@eginformatica02p ~]$ cat test1.sql
\nCREATE OR REPLACE FUNCTION 
da_test.aggairbookingitinasbookedprc(numeric)\nRETURNS void AS\n$BODY$\n\n/* 
Declare Variables*/\n-- modified 2/28/2010\nDECLARE\nvTimestamp 
TIMESTAMP(6);-- Variable to Store Current Timestamp during diffent process 
stage.\nvAuditKey VARCHAR(18);  -- Variable to Capture Lastest 
Auditkey for Specific Dimension Processing.\nvSpStep INTEGER;

Import
d1gp1=> \i test1.sql
psql:test1.sql:1: invalid command \nCREATE
d1gp1=>

--
Replacing \n attempt
d1gp1=> \copy (select 
replace(dba_work.pg_get_functiondef('da_test.aggairbookingitinasbookedprc'::regproc),E'\n','
 ')) to test1.sql WARNING:  nonstandard use of escape in a string literal
LINE 1: ...test.aggairbookingitinasbookedprc' ::regproc ) ,E '\n' , ' '...
 ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
ERROR:  type "e" does not exist
LINE 1: ...a_test.aggairbookingitinasbookedprc' ::regproc ) ,E '\n' , '...
 ^
\copy: ERROR:  type "e" does not exist
LINE 1: ...a_test.aggairbookingitinasbookedprc' ::regproc ) ,E '\n' , '...

d1gp1=> \copy (select 
replace(dba_work.pg_get_functiondef('da_test.aggairbookingitinasbookedprc'::regproc),'\\n','
 ')) to test1.sql WARNING:  nonstandard use of \\ in a string literal
LINE 1: ..._test.aggairbookingitinasbookedprc' ::regproc ) , '\\n' , ' ...



Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CD7625.A87D1690]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] cast name to oid

[Little, Douglas]

I got my function dump function to work.   Enhancing to handle errors if the 
object doesn't exist.

I want to add an exception block, to trap the object not found error.
But when I changed the input parameter type from regproc to text,  I was no 
longer getting matches.
I am trying to explicitly cast the object name as an oid.
Can someone let me know the correct way to do this?

This is failing
   where p.oid = cast(proname as regproc);

NOTICE:  found dba_work.pg_get_functiondef2
WARNING:  sqlstate 42846
WARNING:  sqlerrm cannot cast type text to regproc



Thanks
Current content

CREATE OR REPLACE FUNCTION dba_work.pg_get_functiondef2(proname text)
  RETURNS text AS
$BODY1$
declare
xsource text;
begin
if public.ifexists(proname) then
   raise notice 'found %', proname;
   begin
   select into xsource
   E'\n'
   ||'CREATE OR REPLACE FUNCTION 
'||n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)||')'
   || E'\nRETURNS '||t.typname||' AS'
   || E'\n$BODY$\n'
   || prosrc
  ||E'\n$BODY$\n'
  ||' LANGUAGE ''' || l.lanname
  || E''' VOLATILE;'
  || E'\n alter function 
'||n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)||') 
owner to '||pg_get_userbyid(p.proowner)||';'
  || regexp_replace(replace(E'\n GRANT EXECUTE ON FUNCTION 
'||n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)||') 
TO '
  || array_to_string(proacl,E'\n GRANT EXECUTE ON FUNCTION 
'||n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)||') 
TO ')
 ,' =X',' public=X')
 ,E'=(.*?)(?:\s|$|\n)',E';\n','g')

   from pg_proc p

   inner join pg_type t
   on p.prorettype = t.oid

   inner join  pg_namespace n
   on p.pronamespace = n.oid

   inner join pg_language l
   on p.prolang = l.oid

   where p.oid = cast(proname as name);

   Exception
  when others Then
  xsource = 'Object:'||proname||' not found';
   raise warning 'sqlstate %', SQLSTATE;
   raise warning 'sqlerrm %', SQLERRM;

   end;
end if;

return xsource;
end;

$BODY1$
  LANGUAGE plpgsql STABLE;
ALTER FUNCTION dba_work.pg_get_functiondef2(text)
  OWNER TO dlittle;

Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CD7A35.F48A4490]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] psql & unix env variables

[Little, Douglas]

Is there a method for having unix env variables incorporated into a psql sql 
statement?
Ie
Export var='dev'
Psql =c 'select count(*) from $var.customer;'



Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CD85E2.C7732E50]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [GENERAL] psql & unix env variables

[Little, Douglas]

Thanks


-Original Message-
From: Ryan Kelly [mailto:rpkell...@gmail.com] 
Sent: Wednesday, August 29, 2012 12:41 PM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-general@postgresql.org)
Subject: Re: [GENERAL] psql & unix env variables

On Wed, Aug 29, 2012 at 12:35:32PM -0500, Little, Douglas wrote:
> Is there a method for having unix env variables incorporated into a psql sql 
> statement?
> Ie
> Export var='dev'
> Psql =c 'select count(*) from $var.customer;'
export FOO="bar"
psql -c "select count(*) from $FOO.customer;"

Note the double quotes. That allows your shell to interpolate the string into 
your query. Note that the interpolation is done by your shell, and not psql.

> 
> 
> 
> Doug Little
> 
> Sr. Data Warehouse Architect | Business Intelligence Architecture | 
> Orbitz Worldwide
> 500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | 
> Fax 312.894.5164 | Cell 847-997-5741 
> douglas.lit...@orbitz.com<mailto:douglas.lit...@orbitz.com>
>  [cid:image001.jpg@01CD85E2.C7732E50]   orbitz.com<http://www.orbitz.com/> | 
> ebookers.com<http://www.ebookers.com/> | 
> hotelclub.com<http://www.hotelclub.com/> | 
> cheaptickets.com<http://www.cheaptickets.com/> | 
> ratestogo.com<http://www.ratestogo.com/> | 
> asiahotels.com<http://www.asiahotels.com/>
> 

-Ryan Kelly


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Getting show results into a table

[Little, Douglas]

Is there a way in sql to get the results of the show all command into a table?

I'm expecting  something like
Insert into Config_history as select * from (show all);



Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CDCD65.492B5520]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [GENERAL] Getting show results into a table

[Little, Douglas]

Thanks everybody.Always wondered where the command was sourcing the data.

Thanks

-Original Message-
From: Guillaume Lelarge [mailto:guilla...@lelarge.info] 
Sent: Wednesday, November 28, 2012 3:24 PM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-general@postgresql.org)
Subject: Re: [GENERAL] Getting show results into a table

On Wed, 2012-11-28 at 12:38 -0600, Little, Douglas wrote:
> Is there a way in sql to get the results of the show all command into a table?
> 

SELECT name, setting, short_desc FROM pg_settings

> I'm expecting  something like
> Insert into Config_history as select * from (show all);
> 

INSERT INTO config_history
  SELECT name, setting, short_desc FROM pg_settings;

That should work.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] evaluating expressions stored in table

[Little, Douglas]

Hi,

I need to evaluate an expression that I have stored in a table,  and not sure 
how to force evaluation of a column value.

Some background.  This is a generic testing application that we're using to 
test source to target ETL's.
The specifics of the test expression depend on the actual tables involved.   
Typicallly it's been  table_a.col_a = table_b.col_b   but now they want to use 
inequality or other operators.
The tester's define the test criteria in a table,  then we use that criteria to 
actually score the runtime results.

In my design I have 3 tables.
Test - stores the test definition
Testrun - stores the actual sql for a specific execution of a test
Testscore - stores the actual values of the source and target values.   The 
scores are stored in different rows, with a common name to allow them to be 
matched in the query.

The pass/fail query looks something like this

Update  testscore
Set metricstatus = case when table_a.col_a = table_b.col_bthen 'PASS'   
else 'FAIL' end
..
Where testrunid=x

I want to replace the table_a.col_a = table_b.col_b   with the expression 
stored in the test table and evaluate.

I'm thinking - it's dynamic sql, so I need to build the statement and then 
evaluate using a function.

Anybody have any comments?

Thanks



Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CDDD06.E43D5760]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] ALTER Bigserial error

[Little, Douglas]

Hi,
I'm trying to alter in a bigserial on GP/PG 8.1  I'm getting the error

ERROR: type "bigserial" does not exist
SQL state: 42704

I understand that bigserial isn't a 'true' type, that it's a notational 
convenience.
And that I can alter in the default.

But it is listed in the type table in the doc and I can create new tables with 
it.
I should be able to use it in an alter.
Is there a way to fix this bug?
What would need to change for this issue to be resolved.
Thanks

Doug Little

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CAE475.3ACF6F60]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [GENERAL] ALTER Bigserial error

[Little, Douglas]

Thanks for the response tom,
I agree it's more of an missing feature. 
Regarding the concensus for direction.  I'd like to see the product move in the 
direction of the sql standard. 
Overloading types with macro's probably wasn't a good idea, since it leads 
people like me astray. 

Maybe the doc can be strengthened to include your review. 
Thanks for the help. 

Doug


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Auditing usage

[Little, Douglas]

Hi,

Is there anything in the system that would aid tracking who/how often an object 
(table/column/function/view) is accessed?

In Teradata we have an accesslog table that record attempted access to an 
object and if it's granted or denied.   Very important to our sox's folks.

Thanks

Doug Little

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CAF1F1.45900840]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] object tracking

[Little, Douglas]

Hi,

I have a fairly large data warehouse in Greenplum, and am having trouble 
tracking object changes.
I need to

1.Know when an object was changed, by who.

2.   Have some logging about when an object was dropped - especially drop 
cascade.


Currently I'm having a problem with a set of disappearing views.   We're still 
in pre-prod environment, but

Any suggestions?

I'd like

1.PG to timestamp the catalog tables

2.   Permit triggers on catalog objects.


Doug Little

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CAF759.01607890]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [GENERAL] ALTER Bigserial error

[Little, Douglas]

Scott,
Sorry about the late response.

Curiously, what does the SQL spec have to say about autoincrement and
defaults and such?

>From 
>http://savage.net.au/SQL/sql-2003-2.bnf.html#identity%20column%20specification

...
Define a column of a base table.

  ::=
  [  |  ] [  ]
 [  |  |  ]
 [  ... ] [  ]

  ::=   [  ] 
 [  ]

  ::=
 NOT NULL
 | 
 | 
 | 

  ::=   REFERENCES ARE [ NOT ] CHECKED [ ON DELETE 
 ]

  ::=   

  ::=
 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
 []


-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com]
Sent: Monday, April 26, 2010 3:49 PM
To: Little, Douglas
Cc: Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] ALTER Bigserial error

On Mon, Apr 26, 2010 at 12:59 PM, Little, Douglas
 wrote:
> Thanks for the response tom,
> I agree it's more of an missing feature.
> Regarding the concensus for direction.  I'd like to see the product move in 
> the direction of the sql standard.

Curiously, what does the SQL spec have to say about autoincrement and
defaults and such?

Re: [GENERAL] [SQL] Difference between these two queries ?

[Little, Douglas]

Nilesh, 
They should generate equivalent results, 
But the difference is the constraint on bu.bid=5. 
In the 1st case it's being done after the join. 
In the 2nd case it is being done before the join. 

The end result should be the same, but the execution time can be hugely 
different.
Suppose b has 1b rows, and bu has 50m  with a 20:1 cardinality.   
But bu.bid=5 only select 1 row from bu. 

If the constaint is applied after the tables are joined the db needs to 
materialize the entire 1bx50m row set and then
Select out the bid=5 rows.  

Doug


-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Nilesh Govindarajan
Sent: Saturday, June 05, 2010 9:31 PM
To: PostgreSQL SQL; PostgreSQL General
Subject: [SQL] Difference between these two queries ?

Hi,
I have a doubt about JOINS.

What is the difference between:

1. SELECT b.* from banners b, banners_users bu where b.id = bu.bid and
bu.uid = 5;

2. SELECT b.* from banners b INNER JOIN banners_users bu ON b.id =
bu.bid AND bu.uid = 5;

What is the first type of join called ?

and is it possible that they have different execution times ?

-- 
Nilesh Govindarajan
Facebook: nilesh.gr
Twitter: nileshgr
Website: www.itech7.com
Cheap and Reliable VPS Hosting: http://j.mp/arHk5e

-- 
Sent via pgsql-sql mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] psql dynamic comments

[Little, Douglas]

I'm trying to generate a comment on all my changes - so I know when they were 
completed.

Does anybody have anything that does this.

I think the psql script is something like

d1gp1=> \set currtime `date '+20%y-%m-%d %H:%M:%S'`
d1gp1=> \echo :currtime
2010-10-06 10:01:51

d1gp1=> comment on index mdm_mart_tbls.mdm_gotoastcampaignevt_pk is 'created by 
dwda-1618 on :currtime';
COMMENT


Unfortunately the variable doesn't get replaced due to the quotes.
COMMENT ON INDEX mdm_mart_tbls.mdm_gotoastcampaignevt_pk IS 'created by 
dwda-1618 on :currtime';

I've tried various options,  2,3,4 quotes.  Concatenation, Building the entire 
string as the variable.  But nothing seems to work.

Any suggestions?

Thanks in advance.
Doug

Doug Little

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CB653A.CDF51290]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] column level encryption & select rules

[Little, Douglas]

Hi,
I've implemented a scheme for column level encryption that uses table triggers 
(isrt/update) to encrypt the input data, and a view
To perform the decrypt.   It's working ok, but I'm having trouble altering my 
objects because of the dependents.
To implement the scheme, I have to generate  the view, table trigger 
(isrt/updt), and a trigger function.

Currently the decrypt functions are embedded in the views which I want to get 
rid of.

Can I implement them as a select rule?
If the select rule directs the queries to the same table, does recursion occur?

View is below.
What are the challenges, etc.



Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com

 [cid:image002.jpg@01CA7FD5.13BDD510]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com
-- View: ods_views.customer_payment_profile

-- DROP VIEW ods_views.customer_payment_profile;

CREATE OR REPLACE VIEW ods_views.customer_payment_profile AS
 SELECT customer_payment_profile.customer_payment_profile_id, 
customer_payment_profile.ref_point_of_sale_id, 
customer_payment_profile.last_used_date,
CASE
WHEN owwpiiview() THEN 
dba_work.owwdecrypt(customer_payment_profile.pi2_pii_card_number, 
customer_payment_profile.customer_payment_profile_id::text)::character 
varying(128)
ELSE 
'**'::character 
varying::character varying(128)
END AS pii_card_number,
CASE
WHEN owwpiiview() THEN 
dba_work.owwdecrypt(customer_payment_profile.pi2_pii_cardholder_name, 
customer_payment_profile.customer_payment_profile_id::text)::character 
varying(200)
ELSE 
'**'::character 
varying::character varying(200)
END AS pii_cardholder_name, customer_payment_profile.default_ind, 
customer_payment_profile.ref_payment_type_code, 
customer_payment_profile.expiration_date, customer_payment_profile.active_ind, 
customer_payment_profile.customer_member_id,
CASE
WHEN owwpiiview() THEN 
dba_work.owwdecrypt(customer_payment_profile.pi2_pii_address1, 
customer_payment_profile.customer_payment_profile_id::text)::character 
varying(200)
ELSE 
'**'::character 
varying::character varying(200)
END AS pii_address1, customer_payment_profile.address2, 
customer_payment_profile.address3, customer_payment_profile.address4, 
customer_payment_profile.city, 
customer_payment_profile.ref_state_province_code, 
customer_payment_profile.ref_country_code, 
customer_payment_profile.ref_postal_code, customer_payment_profile.po_box_ind, 
customer_payment_profile.intl_phone_dialing_code,
CASE
WHEN owwpiiview() THEN 
dba_work.owwdecrypt(customer_payment_profile.pi2_pii_phone, 
customer_payment_profile.customer_payment_profile_id::text)::character 
varying(200)
ELSE 
'**'::character 
varying::character varying(200)
END AS pii_phone, customer_payment_profile.phone_extension, 
customer_payment_profile.create_date, customer_payment_profile.modified_date, 
customer_payment_profile.ref_phone_country_code, 
customer_payment_profile.oltp_deleted_timestamp, 
customer_payment_profile.ods_load_timestamp, 
customer_payment_profile.ref_cc_type_code, 
customer_payment_profile.cvn_valid_ind, customer_payment_profile.issue_date, 
customer_payment_profile.pii_issue_number
   FROM customer.customer_payment_profile;

<>

[GENERAL] revoke from all users

[Little, Douglas]

Is there a form of the revoke command that will revoke specific privileges from 
all users?

Thanks


Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com

 [cid:image001.jpg@01CA9DD6.D7E9B3E0]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] SQL select return into PSQL variables.

[Little, Douglas]

Hello,
I want to get a sql select output into a psql variable.  Any ideas how I might 
need to do this.

My script executes a function which returns a TESTID.  I'd like to imbed the 
testid in the script output filenames.

I see that psql can set environment variables with the psql  \i command.
But got any idea how I can get sql output into a psql variable?

Thanks
Doug


psql
orbitz=# \!testvar=1234
orbitz=# \!export testvar
orbitz=# \!echo $testvar
1234
orbitz=# \q
-bash-3.00$ echo $testvar
1234

But I'm unsure how I can get  the pgsql return code into a psql variable
The shell script
current
   psql p1gp1 <>$LOGFile  2>&1
\set ON_ERROR_STOP
select da_test.QATestBuild(false)
QUIT

I'd like to do something like this from my shell script
   psql p1gp1 <>$LOGFile  2>&1
\set ON_ERROR_STOP
select da_test.QATestBuild(false)  into :testid
\!testid=:testid
\!export $testid
QUIT
Echo $testid
1234


Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com

 [cid:image002.jpg@01CAB07C.769E4E80]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] MS Access 2007 update write conflict problem & resolution

[Little, Douglas]

Hi,
We've been struggling with an MS Access 2007 app that updates a PG table.   It 
was working,  and then it wasn't.
It looks like we recreated the table without specifying the precision on the 
timestamp columns.

PG is defaulting to timestamp(6), which doesn't work with MS Access.  The 
finest precision Access can deal with is timestamp(3).
When the record is retrieved from PG it displays 6 microseconds,  when Access 
prepares the update, the value is rounded to 3 microseconds.
The update can't then locate the row  (timestamp mismatch), and displays  the 
'write conflict' error.

Altering the column to timestamp(0) corrected the issue.

2 questions

1-   PGAdmin was reporting the precision as 0, while a select was 
displaying 6 microseconds.  Is this a known bug in pgadmin?

2-  Is there a way in PG that I can set the default precision on timestamp?
Thanks

I'll cross post to the pgadmin list for the pgadmin question.

Doug Little

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CAC74F.9EF04250]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] hash options

[Little, Douglas]

Hello,

I'm working on a data warehouse dimensionalization process   where I need to 
hash a text string to use as the key.
I've implemented with MD5.  It works fine,  the problem I have is the size of 
the md5 (32 bytes) is often longer than the original string - thus not 
accomplishing what I want - space savings.

Does anybody have alternative hash function recommendations?
 I looked at the options I knew of
select length(encode('ar=514','hex')); -- 12
select length(decode('ar=514','base64')); -- 24
select length(DIGEST('ar=514', 'md5')) -- 16bytes
select length(DIGEST('ar=514', 'sha1')) -- 20bytes

function is currently written in pg/plsql,  but I'm considering switching to 
python for broader library choice.



Source data is delimited list of name/value pairs.  Length range from 0-2500 
bytes.
ar=514,cc=CA,ci=Montreal,cn=North+America,co=Sympatico,cs=Canada,nt=Xdsl,rc=QC,rs=Quebec,tp=High,tz=GMT%2D5

Thanks in advance
Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CCD8EB.FDD3C490]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] help with normalizing

[Little, Douglas]

Hello,
Need some help.  Hoping some of the smart people might know how to solve this.

I'd like to replace all name/value pairs in a delimited string  with the id of 
the name/value pair in my reference table.
Eg
St=IL&city=Chicago&street=Madison
To
13&50&247
Assuming  St=IL  is id 13,  city=Chicago=50, street=Madison=247

My process is working but it's taking too long to convert the string to rows. 
Any ideas for swaping out the cursor for a  sql trick?
Thanks in advance
Doug


I'm currently cursoring thru the input rows, and then converting the string to 
rows using  1st array_to_string,  now explode_array.
-- Current code
   sql_cmd := ' SELECT   hash_page , log_cs_uri_query  FROM dim_wtlog_page_temp 
';
   FOR recset IN EXECUTE sql_cmd LOOP
insert into pagetag_temp (hash_page, wtlog_tagvalue_text)
 select recset.hash_page   ,qry.* as wtlog_tagvalue_text
 from explode_array (string_to_array(recset.log_cs_uri_query,'&') ) qry 
 ;
   END LOOP;

create or replace function explode_array( in_array anyarray)
returns setof anyelement as
$$
select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$$
language sql immutable;

Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CCDDD9.A969D9B0]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] normalizing & join to table function

[Little, Douglas]

Hello,
Need some help.  Hoping some of the smart people might know how to solve this.

I'd like to replace all name/value pairs in a delimited string  with the id of 
the name/value pair in my reference table.
Eg
St=IL&city=Chicago&street=Madison
To
13&50&247
Assuming  St=IL  is id 13,  city=Chicago=50, street=Madison=247

My process is working but it's taking too long to convert the string to rows. 
Any ideas for swaping out the cursor for a  sql trick?
Thanks in advance
Doug

I'm currently cursoring thru the input rows, and then converting the string to 
rows using  1st array_to_string,  now explode_array.
-- Current code
   sql_cmd := ' SELECT   hash_page , log_cs_uri_query  FROM dim_wtlog_page_temp 
';
   FOR recset IN EXECUTE sql_cmd LOOP
insert into pagetag_temp (hash_page, wtlog_tagvalue_text)
 select recset.hash_page   ,qry.* as wtlog_tagvalue_text
 from explode_array (string_to_array(recset.log_cs_uri_query,'&') ) qry 
 ;
   END LOOP;

create or replace function explode_array( in_array anyarray)
returns setof anyelement as
$$
select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$$
language sql immutable;

Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CCE011.A46685F0]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>