Re: [SQL] sorry, now with subject... trigger & nextval(seq)

etween the sequence, will create the sequence and will drop everything if the column gets dropped. Regards, Daniel Migowski <> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] procedure help between databases

Hello list, I need some help in writing a trigger / procedure. I have two databases, one for last.fm and one for musicbrainz. What I would like to do is when i submit a song into last.fm, connect to the musicbrainz and get an id for the song. Both are using postgres and i can use a db link to qu

[SQL] viewing the description of tables from python DB-API

Hi all,   Is there a way to view the list of all tables from python (or any other languages for that matter) DB-API?  What I’m looking for is a command similar to the meta-command ‘\d’ that works with the psql client.    Thanks very much!   Dan  

[SQL] SQL92 compliance

y a client application, at the end of a several data marshalling processes, doesn't get the right data... For instance MySQL doesn't require it and sometimes that sucks: SELECT my_column1 my_column2, my_column3 FROM my_table Regards, -- Daniel --

[SQL] Evaluation of if conditions

, which let me think that PL/PGSQL evaluates all the conditions: IF (TG_OP = 'INSERT') OR (OLD.bar = ...) THEN statement END IF; Should be rewritten as (for example): IF (TG_OP = 'INSERT') THEN statement ELSIF (OLD.bar = ...) THEN statement END IF;

[SQL] deleting rows in specific order

Hi, What is the best way to do something like: delete from tablename where something order by somefield... Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] deleting rows in specific order

is a little bit more dificult which I would be able to simplify if rows are deleted in date order. Now I'm doing it in a plpgsql function using a `for in select loop' to delete rows one by one. Daniel ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] migrating numeric to serial from MSSQL to postgres

XVALUE START WITH 1 CACHE 1 NO CYCLE; Then you should be able to migrate your code to something like: Id INTEGER NOT NULL DEFAULT NEXTVAL(my_sequence') -- Daniel ---(end of broadcast)--- TIP 1: if posting/reading through Usenet,

Re: [SQL] migrating numeric to serial from MSSQL to postgres

reason?): CREATE TABLE my_table ( Id INTEGER NOT NULL, ... ); [DTS process here] CREATE SEQUENCE my_sequence INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH CACHE 1 NO CYCLE; ALTER TABLE m_table ALTER COLUMN Id SET DEFAULT NEXTVAL('my_sequence'); Does that make

Re: [SQL] conversion of numeric field from MSSQL to postgres

stgres. How would i migrate > this: > > [Id] [numeric](18, 0) IDENTITY (1, 1) > It seems that you've already asked for this question last Sunday, and because your question is somewhat deterministic, the answers are more likely to be the same. Check your previous e-mails. --

[SQL] DELETE RETURNING

FROM b RETURNING x, current_timestamp; IF NOT FOUND THEN -- No data were deleted actually END IF; Thanks, -- Daniel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] help with version checking

ACE FUNCTION check_version() RETURNS void AS $$ DECLARE v_version VARCHAR; BEGIN SELECT version INTO v_version FROM version WHERE id = 1; IF v_version <> '1.0.0.0' THEN RAISE EXCEPTION 'This script needs Agenda version 1.0.0.0, detected version %&#

[SQL] Sequence vs Serial

S (v_mycolumn1, v_mycolumn2); SELECT INTO v_mytable_id currval('seq_mytable_id'); RETURN v_mytable_id; -- Daniel

Re: [SQL] Calling void functions

> I'm informed that the last statement of a function that returns void > cannot be > a SELECT. How else is one supposed to call another function which also > returns void? > PERFORM ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Calling void functions

> PERFORM works only in plpgsql, Peter wrote a pl/sql-function... > Oups, sorry! I missed the point. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] pg & Delphi

well, but it is too slow. Daniel ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

[SQL] array handling on 8.0.3

): INFO: splitted=b INFO: a={a,b} INFO: splitted= INFO: a= INFO: a= ready It looks that value of the array `a' is lost after the loop. Is this normal? What do I do wrong? Daniel ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Re: [SQL] array handling on 8.0.3

On Tue, 9 Oct 2007, Daniel Drotos wrote: I have to run a function on a 9.0.3 server. Following part (of a bigger ^ I mean 8.0.3 sorry Daniel ---(end of broadcast)--- TIP 6: explain

Re: [SQL] array handling on 8.0.3

Hi, I found my bug, please forget my (stupid) question! Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] Computed table name in function

On Wed, 10 Oct 2007, Ray Madigan wrote: I thought that the documentation said I couldn't use EXECUTE on a SELECT INTO? Try "for record_var in select ..." Look for 36.7.4 Looping Through Query Results in the docs. Daniel ---(e

[SQL] Accessing field of OLD in trigger

Hi, I'm working on a row level plpgsql trigger running after delete, using a 8.0.3 server. It gets a parameter which is a field name of the OLD record. How can that field be accessed? I'd like to do something like: for recvar in 'select OLD.'||quote_ident(TG

[SQL] Accessing field of OLD in trigger

Hi, I'm working on a row level plpgsql trigger running after delete, using a 8.0.3 server. It gets a parameter which is a field name of the OLD record. How can that field be accessed? I'd like to do something like: for recvar in 'select OLD.'||quote_ident(TG_ARGV[0])..

[SQL] Erlang & PostgreSQL native driver

u use? Does someone use the PostgreSQL driver from Erlang Consulting? Are there other native PostgreSQL driver? Thanks, -- Daniel ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail co

[SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE

e | value | isLocked | timestamp | type | tokenName -+--+--+--+----- ---+--+--- 104 |2 | RegressionTestClanName13 | f| 2007-11-27 20:40:25.208074 |2 | clanName 40 |2 | Regressio

Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE

> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : mardi, novembre 27, 2007 23:46 > À : Daniel Caune > Cc : pgsql-sql@postgresql.org > Objet : Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE > > "Daniel Caune" <[

Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE

> De : Tom Lane [mailto:[EMAIL PROTECTED] > > "Daniel Caune" <[EMAIL PROTECTED]> writes: > > I did the following test, removing all the where-clause from the SELECT > statement. Every statement completes immediately, i.e. it doesn't block. > > I

[SQL] Using schema

, bar WHERE foo.i = bar.i; -- Uses both schemas That is damn flexible! :-) -- Daniel ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your

[SQL] PL/pgsql: function passing argument to IN operator

Can anyone help me out? Thanks, Daniel P.S.: Sorry if capitalizing SQL keywords is bad list etiquette... ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] JOIN a table twice for different values in the same query

js=# SELECT departure_date, departure.code AS departure_code, arrival.code as arraival_codeFROM jsjourneys         JOIN jsports as departure ON jsjourneys.departure_port = departure.id        JOIN jsports as arrival on jsjourneys.arraival_port = arraival.id LIMIT4; Regards,Daniel

[SQL] SQL stored function inserting and returning data in a row.

008-01-10 22:08:48 EST CONTEXT: SQL function "create_matchmaking_sesssion" I can easily convert this code into a PL/pgSQL function, but I'm thinking that pure SQL is more natural (and faster?) for such a stored function. Regards, -- Daniel --

Re: [SQL] SQL stored function inserting and returning data in a row.

tatements to the SQL engine for execution. There is a little overhead between PL/PLSQL and SQL engines. Regards, -- Daniel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

[SQL] What does mod - in "mod statement" - stand for?

nlighten me. Thanks, -- Daniel http://www.majormode.com/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] function that returns a set of records and integer(both of them)‏

Hi Everyone I would like to know if i can create a function that returns a set of record with the sql statement: select and a integer, i mean both of them, because i remenber that in sql server (transact sql) i can do that in a stored procedure doing a select statement and a return of a integer

RE: [SQL] function that returns a set of records and int eger(both of them)‏

h of them)‏> CC: pgsql-sql@postgresql.org> > 2008/7/11 daniel > blanco <[EMAIL PROTECTED]>:> > Hi Everyone> >> > I would like to know if i > can create a function that returns a set of record> > with the sql statement: > select and a integer, i

RE: [SQL] function that returns a set of records and int eger(both of them)‏

:07 +0200> From: [EMAIL PROTECTED]> To: [EMAIL > PROTECTED]> Subject: Re: [SQL] function that returns a set of records and > integer(both of them)‏> CC: pgsql-sql@postgresql.org> > Hello> > 2008/7/13 > daniel blanco <[EMAIL PROTECTED]>:> > Ok, than

Re: [SQL] Join question

have you tried a right Join?Daniel Hernndez.San Diego, CA."The more you learn, the more you earn".Fax: (808) 442-0427-Original Message-From: "Edward W. Rouse" [EMAIL PROTECTED]: 08/15/2008 09:48 AMTo: [EMAIL PROTECTED]: Re: [SQL] Join question I have 2

Re: [SQL] postmaster: init.d/start

ink the pg > start/stop script? > Why should one link it for more than one runlevel (I noticed this for > apache)? > > Thanks, > > Markus > > Daniel Fairs [EMAIL PROTECTED], [EMAIL PROTECTED] http://www.darkalley.co.uk, http://listen.to/agentorange Just because you're not paranoid doesn't mean they're not out to get you.

Re: [SQL] Extracting data by months

#x27;, date_field) will always result in the 1st monthday of the month. The year will be preserved. Daniel >>>John McKown said: > This might seem rather silly, but could you simply do something like: > > select * from database >where date_field >= '01/01/

Re: [SQL] Extracting data by months

This is not exactly what the original question was about. You select the month is one specific year only. What you do it however easier achieved by: SELECT datums FROM jaunumi WHERE date_trunc('month', datums) = '2000-08-01'; datetime is an type full of tricks. :-) Dani

[SQL] Internal functions documented

I've noticed a lot of the functions that are documented for libpg are also stored in pg_proc. Are these functions additionally callable from pl/pgsql? And if so, are they documented for pl/pgsql anywhere, or do we need to make educated guesses as to the parameters for them under pl/pgsql? For

[SQL] Compiling pl/pgsql functions?

Through preliminary examination, it appears stored procedures using pl/pgsql in PostgreSQL 7.3 are interpreted, rather than compiled? When we insert a stored function into PostgreSQL, the syntax check is not done until execution, and only on the branch/path of execution, not on the entire funct

[SQL] How to have a unique primary key on two tables

vice versa. A sort of primary key in common for both tables. Any hints? Thank you very much -- Daniel "bodom_lx" Graziotin - http://daniel.graziotin.net - http://daniel.graziotin.net/bodom_lx.asc - GPG public key ---(end of broadcast)---

[SQL] Calculating with the time

Hello All, I am looking for an advice how to do calculation with the time. I do have a column with datatype 'timestamp with time zone'. The value is '2003-03-22 07:53:56-07' for instance. I would like to select it from the table with result of '07:59:59' – '07:53:56', so the query should return

[SQL] Simple SQL question

Hello, everybody. I've a simple question about SQL usage but i don't know even i can formulate this question. Well, i will try : I've a teacher_course table with columns cd_course, cd_teacher => teacher_course(cd_teacher,cd_course) and i've a set of pairs that contains the values for t

[SQL] Functional index and string concatenation

Is there a way to create a "functional index" over a string concatenation of two columns ? Thanks. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Simple SQL question

Thank you, Jeremy. I've built a function that returns a string from (cd_teacher, cd_course) and i've create a functional index over this function, like : create index teacher_course_idx on teacher_course (build_unique_2p(cd_teacher,cd_course)); select * from teacher_course where build_unique_2p

[SQL] Help to simplify sample query

Hi guys, i have a new question about how to simplify a query. I have the tables area_course(cd_area,cd_course) and teacher_course(cd_teacher,cd_course) and a set of pairs of values {(1,2),(98,45),(11,0),...}. Now, i must to select the areas which courses appears in teacher_course and mat

Re: [SQL] Help to simplify sample query

Tomasz Myrta wrote: Can you try this query ? : select cd_area from area a1 join teacher_course c2 using (cd_course) where (cd_teacher,cd_course) in (select 1,2 union select 98,45 union select 11,0); Tomasz, the "in" in the query above will work like an "or" ((cd_teacher=1,cd_course=2) or (c

Re: [SQL] Help to simplify sample query

How about this: select cd_area from area a1 where not exists ((select 1,2 union select 98,45 union select 11,0) except select c2.cd_teacher,c2.cd_course from teacher_course c2 where c2.cd_course=a1.course) ? Tom Lane wrote: Daniel Henrique Alves Lima <[EMAIL PROTECTED]> writes:

Re: [SQL] Help to simplify sample query

Where can i find the documentation about "except" or "textcat" ? I've looked at html docs in my machine (i've a 7.34 copy) and i couldn't found information/sample of them. Thanks. ---(end of broadcast)--- TIP 7: do

[SQL] About pg_dump

Hi, everybody ! I don't know if this is the best list to ask this question but if it doesn't, please forgive me (should i try pgsl-general, maybe ?)... We have a postgresql database in production and i've 2 questions about pg_dumpall boring me. We "dump" both ddl and data as SQL command

Re: [SQL] About pg_dump

I've made a test and it seems ok to me. I'm using utf-8 encoding under postgresql 7.3.3. Tom Lane wrote: "Rute Solipa" <[EMAIL PROTECTED]> writes: it seems that the pg_dump command doesn't keep the encoding information. As of 7.4.2 it does emit a SET client_encoding. In prior release

Re: [SQL] where not unique

Gary Stainburn wrote: Hi folks, I've got a table which contains amongst other things a stock number and a registration number. I need to print out a list of stock number and reg number where reg number is not unique (cherished number plate transfer not completed). I've tried variations of a

Re: [SQL] where not unique

You're right, Reinoud. Sorry for my mystake... Reinoud van Leeuwen wrote: On Fri, Mar 12, 2004 at 10:54:25AM -0300, Daniel Henrique Alves Lima wrote: Gary Stainburn wrote: Hi folks, I've got a table which contains amongst other things a stock number and a registration numbe

<    1   2