[GENERAL] Stumped on windowing

I'm working with a product that uses effective date based data structures. We then create views using analytic functions that have begin and end dates for when that record was valid. This works fine when there is just one record per item that is valid at any given time (for instance job

Re: [GENERAL] XML import with DTD

Post a snippet of the xml and xpath you are trying to use. Scott - Original Message - From: Roy Walter w...@brookhouse.co.uk To: pgsql-general@postgresql.org Sent: Friday, July 10, 2009 7:49:00 AM GMT -08:00 US/Canada Pacific Subject: [GENERAL] XML import with DTD Hi I'm

Re: [GENERAL] Custom runtime variables

- Original Message - From: Andreas Kretschmer akretsch...@spamfence.net To: pgsql-general@postgresql.org Sent: Friday, June 26, 2009 1:58:08 AM GMT -08:00 US/Canada Pacific Subject: Re: [GENERAL] Custom runtime variables Scott Bailey arta...@comcast.net wrote: Thanks Tom. So how

Re: [GENERAL] masking the code

Obfuscated function source code (not wanted) Obfuscating function source code has minimal protective benefits because anyone with super-user access can find a way to view the code. At the same time, it would greatly complicate backups and other administrative tasks. To prevent non-super-users

Re: [GENERAL] Naming functions with reserved words

Uh, what project is that exactly, and was it even working within Postgres? The project is http://pgfoundry.org/projects/temporal/ But it looks like I'm just stupid or confused (or confused and stupid). I'm working on porting temporal extensions I wrote originally for Oracle to Postgres.

Re: [GENERAL] Converting each item in array to a query result row

- Original Message - From: Postgres User postgres.develo...@gmail.com To: pgsql-general pgsql-general@postgresql.org Sent: Friday, May 29, 2009 12:21:11 AM GMT -08:00 Tijuana / Baja California Subject: [GENERAL] Converting each item in array to a query result row Hi, I'd writing

Re: [GENERAL] Aggregate Function to return most common value for a column

I want to use an aggregate function that will return the most commonly occurring value in a column. It's actually dead simple in Postgres. No C either. You just need to create an aggregate function. I wrote a most() aggregate a while back that does exactly what you are asking for. Here,

Re: [GENERAL] Aggregate Function to return most common value for a column

On Fri, May 22, 2009 at 03:23:07PM +, arta...@comcast.net wrote: I want to use an aggregate function that will return the most commonly occurring value in a column. It's actually dead simple in Postgres. No C either. You just need to create an aggregate function. I wrote a most()

Re: [GENERAL] How to split timestamps values by 24h slices ?

INTO calendar SELECT '2000-01-01'::date + i FROM generate_series(0,1) i; Now change your original query like so: SELECT * FROM my_table JOIN calendar ON calendar_date BETWEEN TRUNC(timestamp_start) AND timestamp_end Artacus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] Postgres Cookbook

Stefan Kaltenbrunner wrote: Tino Wildenhain wrote: Greg Smith wrote: On Wed, 4 Mar 2009, Artacus wrote: So it looks like at one time we had a cookbook. But the links are dead now. I'm not sure why Roberto Mello stopped hosting that, but you can see the last content posted there at http

[GENERAL] Postgres Cookbook

So it looks like at one time we had a cookbook. But the links are dead now. Does anyone know what ever happened to that, or if it lives on somewhere else? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] PostgreSQL to Oracle

Hi All, I want to migrate from PostgreSQL to Oracle and need any tool preferably open source. And I am specially concerned with stored procedures / functions. Regards, Abdul Rehman. You were just converting from Oracle to Postgres two days ago, so it shouldn't take much to convert back.

Re: [GENERAL] weakness and strenghts of PG

a web host that runs the current version of Postgres. The other day I was going to do a small project in Ruby and Postgres and I spent a couple hours trying to get Ruby talking to Postgres on Windows. It's going to be impossible for Postgres to compete with MySQL while this continues. Artacus

Re: [GENERAL] Update with a Repeating Sequence

academic now. But this is a great use case for the windowing functions being added to 8.4. In 8.4 it should be as easy as SELECT field_id, RANK() OVER(PARTITION BY field_id) AS seq FROM foo; Artacus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?

; END; So, is it true that as of Postgresql 8.3 there is no way to have a pgpqsql function return multiple SELECTs? Vladimir Dzhuvinov Have you considered returning XML instead? You should be able to get what your looking for much easier with an XMLAGG. Artacus -- Sent via pgsql-general

Re: [GENERAL] Need schema design advice

a secondary one. Artacus -- 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 variable quoting

, it doesn't expand the variables. I can set a import_file variable like so: \set import_file '/tmp/:tbl.csv' That expands properly but isn't quoted when used in the COPY command. How do I do this? Artacus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] Getting rows in statement-level triggers

So the manual says there is no way for a statement-level trigger to examine the row(s) modified by the statement. Is there any way to get the xmin or cmin of the transaction that fired the trigger? Or can I look up the last xid for a table some where? Ok, so it took a lot of googling to

Re: [GENERAL] Getting rows in statement-level triggers

? Is xmin indexed? Can it be? BTW, if there were no demand for a convenient way to get your xid, then there shouldn't be a traditional way :) Artacus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

[GENERAL] Getting rows in statement-level triggers

So the manual says there is no way for a statement-level trigger to examine the row(s) modified by the statement. Is there any way to get the xmin or cmin of the transaction that fired the trigger? Or can I look up the last xid for a table some where? -- Sent via pgsql-general mailing list

Re: [GENERAL] Getting rows in a very specific order

If I use the value of the hierarchy column in a query I can get all the rows that a given row is an descendant of. (SELECT * FROM items WHERE itm_id IN (1,31,68,97), for example. However, I need the rows to be in the correct order, ie the root node first, child second, grandchild third etc.

Re: [GENERAL] weekday from timestamp?

..::rDk::.. wrote: im strugling with my dataset.. got a small pgsql db with a timestamp column in format :MM:DD HH:MM:SS for each record Use to_char to_char(tscol, 'dy') - mon to_char(tscol, 'Day') - Monday to_char(tscol, 'D') - 2 -- Sent via pgsql-general mailing list

[GENERAL] If there were no OS databases

applications and I was hooked. But if MySQL and Postgres didn't exist, any apps I made would have been pretty boring and I'd still be configuring routers (yuck). So what about you? How would your world be different? Artacus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] declare column update expression

is that it won't FORCE the value like it would with a trigger. So while the trigger would happen automatically, using this approach, you'd have to remember to also update that field any time you did an update. Artacus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

Re: [GENERAL] declare column update expression

That means I have to then go through all my code and make sure I set the fields value. If I forget to modify one statement, things will break. Right, that's why the right answer for what you want to do is to have a trigger. I was just giving you an alternative since you didn't like the

Re: [GENERAL] psql scripting tutorials

$ TEST=16; psql -c select $TEST as \input1\; $ TEST=16; echo select $TEST as \input1\; | psql Yep that works. My coworker also suggested using EOF to simulate a psql script. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] psql scripting tutorials

Artacus wrote: I'd like to learn a little more about writing psql scripts does anyone know of any resources outside of the manual? Ok then. Does anyone have any tips or best practices for scripting psql? I'll probably write some bash scripts to pull csv files over then script psql to do

Re: [GENERAL] How to upload data to postgres

Markova, Nina wrote: Hi again, I need to load data from Ingres database to Postgres database. What's the easiest way? Thanks, Nina Easiest way would be to export to CSV and import using COPY. Slickest way would be to use something like dblink. -- Sent via pgsql-general mailing list

Re: [GENERAL] psql scripting tutorials

This sounds a lot like what I did in my last job using bash for most things, and php for the more complicated stuff. Wrote a simple oracle to pgsql table replicator in php that worked pretty well. Well we do this stuff all the time with Oracle and sql*plus. And I've heard people hear say

Re: [GENERAL] using a GUI front end to postgres

What's the best open-source front-end for rapid GUI query and report generation using postgres? Is it possible to use MS access as a front-end to postgres for rapid prototyping? Can that be done through ODBC? This question was asked about a week ago. I don't recall all of the answers but

Re: [GENERAL] abusing plpgsql array variables

If I want to pass in a text[] argument to a plpgsql function, at what array size am I asking for problems? 100? 10,000? 100,000? What severity of problems might I encounter? Bad performance? Postgres refusing to run my query? A crashed backend? Yeah, like you I was pretty worried about how

Re: [GENERAL] No error when column doesn't exist

I expected that to generate an error: column foo.text does not exist. Instead it treats foo.text as foo.*::text AS text: Is this a feature or a bug? Hmm. It's a feature, but maybe a dangerous one. The expression is being treated as text(foo), which is intentional in order to allow use of

[GENERAL] psql scripting tutorials

I'd like to learn a little more about writing psql scripts does anyone know of any resources outside of the manual? -- 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 affected rows in pgplsql

I'm writing some ETL procedures in pgplsql. After each insert/update/delete, I'd like to log how many rows were affected. I'm not finding anything on Google. Does anyone know how to get this? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] Oracle and Postgresql

I'm running all 8.3. But I don't think it makes a difference. There is some geometry type cube function but its not at all like the OLAP cube that I'm talking about. What version of Postgres are you running? On Wed, Sep 3, 2008 at 10:21 PM, Artacus [EMAIL PROTECTED] mailto:[EMAIL PROTECTED

Re: [GENERAL] Oracle and Postgresql

So that is for real huh? I've been to that web site before and figured it was more marketing talk about what they wanted to do rather than a product that was already to market. 2008/9/4 Artacus [EMAIL PROTECTED]: Oh, as I was writing a CUBE query today and realized that I forgot to mention

Re: [GENERAL] Getting affected rows in pgplsql

That's just what I needed. Thanks guys! Artacus On Thu, Sep 04, 2008 at 02:07:01AM -0700, Artacus wrote: I'm writing some ETL procedures in pgplsql. After each insert/update/delete, I'd like to log how many rows were affected. http://www.postgresql.org/docs/current/interactive/plpgsql

Re: [GENERAL] Oracle and Postgresql

Oh, as I was writing a CUBE query today and realized that I forgot to mention this. And unlike most gripes, like MERGE INTO or CTE's which are really convenience things, this is key piece of functionality that you just can't reproduce in Postgres. That said, there's not the same sense of

Re: [GENERAL] Oracle and Postgresql

I can't speak from the dba perspective, but I mainly write applications against Postgres and Oracle. I've used a dozen or more RDBMS's and Postgres and Oracle are by far the most similar of any two. When the two differ, its about an even split for when I say I wish Oracle did it like Postgres

Re: [GENERAL] Custom sort

Can you define a custom sort in postgres? For instance in mysql, you could do something like (I forget the exact syntax) ORDER BY FIND_IN_SET(column_name, ('one','two','three')) I don't really know this syntax but isn't it something like : ORDER BY column_name='one', column_name='two',

[GENERAL] Custom sort

Can you define a custom sort in postgres? For instance in mysql, you could do something like (I forget the exact syntax) ORDER BY FIND_IN_SET(column_name, ('one','two','three')) Art -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Substitute a variable in PL/PGSQL.

You can do it in straight sql like so. SELECT (array[col1, col2, col3, col4, col5, col6, col7, col8, col9, col10])[i] FROM test t, generate_series(1,10) i Art -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] A couple of newbie questions ...

This is one of the many SQL bad habits you've likely picked up from using MySQL. I'd highly suggest reading the pgsql users manual cover to cover, you'll pick up a lot of good info on how to drive postgresql. Other things that work in mysql but fail in pgsql include inserting things that are

Re: [GENERAL] array sort for varchar arrays?

I'm likely overlooking something, but I can't seem to find a function to sort a varchar array. Something like select sort('{y,z,x}'::varchar[]) = {'x','y','z'}. You've got it right. On Pg 8.3.3 I get SELECT sort(array['z','y','x']) - {x,y,z} Crap. There's a sort function included for

Re: [GENERAL] Getting source code for database objects

Easier would be just uing pg_dump -s schema.sql to get all schema objects so you could check them into subversion. If you want only specific objects, pg_dump -l listofobjects, then edit this list as you like and use pg_dump -L listofobjects someobjects.sql The -l and -L options are not

Re: [GENERAL] array sort for varchar arrays?

I'm likely overlooking something, but I can't seem to find a function to sort a varchar array. Something like select sort('{y,z,x}'::varchar[]) = {'x','y','z'}. You've got it right. On Pg 8.3.3 I get SELECT sort(array['z','y','x']) - {x,y,z} -- Sent via pgsql-general mailing list

[GENERAL] Getting source code for database objects

with pgadmin and wireshark trying to figure out what commands or queries it is using to no avail. Artacus -- 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] Date Formatting for dd/mm/yyyy

the records out. The way dates are displayed is dependent on (and configured in) the client not on the server. So if you have a specific format you want your dates displayed then you need to convert them to a string in your query. Artacus -- Sent via pgsql-general mailing list (pgsql

Re: [GENERAL] Probably been asked a hundred times before.

I'm using Ubuntu for my development server. The live update updated postgres either the day of or the day after 8.3.3 came out. Can't complain about that. Artacus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Problem with volatile function

You can force Pg to re-evaluate random() by adding a dummy parameter that depends on the input record, or (probably better) by writing a variant of it that tests the input against a randomly generated value and returns a boolean. Eg: Thanks all. So here's the situation. I added a dummy

Re: [GENERAL] Sequences

I want to create a sequence that increases in unit column 3 for each record individually in column 2 How do i create a sequence that can manage this? Is there a solution for this? Yeah, depesz shows how to do this here

[GENERAL] Problem with volatile function

So my understanding of volatile functions is that volatile functions can return different results given the same input. I have a function random(int, int) that returns a random value between $1 and $2. I want to use it in a query to generate values. But it only evaluates once per query and

[GENERAL] Additional arguments to aggregate functions

Is there a way to send additional arguments when defining a custom aggregate? I wrote a mysql style group_concat aggregate. You get DISTINCT for free. I want to be able to define if it is sorted and what the separator is. Art -- Sent via pgsql-general mailing list