[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 assignme

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" 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 trying to use the XPath f

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 f

Re: [GENERAL] Custom runtime variables

- Original Message - From: "Andreas Kretschmer" 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 wrote: > Thanks Tom. So how about accessing it from from plpgsql or sq

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

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

- Original Message - From: "Postgres User" To: "pgsql-general" 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 a query against a function (pg_proc) that contains 2

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 m

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, I'

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

es INSERT 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 (pgsq

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

[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: http://www.postgresql.org/mailpr

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

Or try finding 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 c

Re: [GENERAL] Update with a Repeating Sequence

.. I know its 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

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

experts on this list is welcome. I tend to favor the second approach because it is more extensible. I might add an additional field to the preferences table. Something like preference_order so that you can record someone's primary pick from a secondary one. Artacus -- Sent via pgsql-gene

[GENERAL] psql variable quoting

emove the spaces, 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

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

it to query based on xmin? 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 subscript

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 fig

[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 (p

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 (pgsql-gener

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

[GENERAL] If there were no OS databases

driven web 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-genera

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

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 tr

Re: [GENERAL] declare column update expression

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

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 f

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] 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 I

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 about

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 (pg

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 ps

[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

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/inte

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

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 PROTECTE

[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 subscript

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 comm

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', colum

[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: http

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 out

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: http://www.postgresql.org/ma

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 reco

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 in

[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] 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 (pgsql-g

Re: [GENERAL] Date Formatting for dd/mm/yyyy

n you read 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 l

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 subscrip

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 http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-s

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 param

[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 not

[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 (pgsql-general@postgresq