al 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 sugg
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
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
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
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 review
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
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 fin
Visit/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...@pinpointresea
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
Fq
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
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
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 Chi
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, Li
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 606
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 c
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 inclu
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.516
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
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 wi
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 i
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/deb
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 someth
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=Chica
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=Chicag
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 wan
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 cata
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-
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
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 ha
iginal 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
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
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
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
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
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 p
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
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
do
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
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
re
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
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
41 matches
Mail list logo
