[SQL] Viewing stored procedure code
Hi : 1) How do I view the body of a stored procedure in psql? 2) How do I know get the corresponding stored procedure of a particular trigger in psql? thanks in advance, ludwig. __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Viewing stored procedure code
On Thu, 10 Oct 2002, Ludwig Lim wrote: > Hi : > > 1) How do I view the body of a stored procedure in > psql? SELECT prosrc from pg_proc where proname = 'your pl/pgsql procedure name'; > > 2) How do I know get the corresponding stored > procedure of a particular trigger in psql? > SELECT t.tgname,f.proname from pg_trigger t,pg_proc f where t.tgname = 'your trigger name' and t.tgfoid = f.oid; > thanks in advance, > > ludwig. > > __ > Do you Yahoo!? > New DSL Internet Access from SBC & Yahoo! > http://sbc.yahoo.com > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL Error
> CREATE TABLE "scheduler_action" (
>"scheduler_action_id" numeric(30) NOT NULL,
>"action_type" numeric(4) NOT NULL,
>"priority" numeric(4) NOT NULL,
>"referenced_id"numeric(30) NOT NULL,
>"invocation_date" numeric(30) NOT NULL,
>"is_done" numeric(1) NOT NULL,
>PRIMARY KEY ("scheduler_action_id")
> );
>
> My quety look so:
>
> select t0_o.scheduler_action_id from scheduler_action t0_o where
> t0_o.is_done = 0 and t0_o.invocation_date <= 1034033214921
>
> And I get following error:
>
> ERROR: Unable to identify an operator '<=' for types 'numeric' and
'double
> precision'
> You will have to retype this query using an explicit cast
>
> But if my query looks like:
>
> select t0_o.scheduler_action_id from scheduler_action t0_o where
> t0_o.is_done = 0 and t0_o.invocation_date <= '1034033214921'
>
> "invocation_date" is numeric with p=30 and s=0.
>
I've got the same error message on
select version();
version
---
PostgreSQL 7.2.1 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
but
select t0_o.scheduler_action_id from scheduler_action t0_o where
template1-# t0_o.is_done = 0 and t0_o.invocation_date <=
1034033214921::numeric ;
did work;
I cannot see why this is necessary, and I cannot understand why nobody
else
replied to both of your requests.
Regards, Christoph
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL Error
select t0_o.scheduler_action_id from scheduler_action t0_o where t0_o.is_done = 0 and t0_o.invocation_date <= 1034033214921::numeric; seems to work in PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 The reason that this query worked in hpux without the explicit casting is maybe hpux runs on a 64-bit architecture processor == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SELECT with INNER data bases
Hi, I have two data bases: db1 and db2. In MS SQL Server I can use 'SELECT T1.ID, T2.NAME FROM db1..table1 T1 INNER JOIN db2..table2 T2 ON T1.ID = T2.ID ...'. How I do it in the PosgreSQL ? HELP ME, PLEASE ! Sorry, I'm a Brazilian and I don't speak and write English very well ! Silvio 2000Info
Re: [SQL] SELECT with INNER data bases
On Thu, Oct 10, 2002 at 09:37:49 -0300, 2000 Informática <[EMAIL PROTECTED]> wrote: > Hi, > > I have two data bases: db1 and db2. > In MS SQL Server I can use > 'SELECT T1.ID, T2.NAME FROM db1..table1 T1 INNER JOIN db2..table2 T2 > ON T1.ID = T2.ID ...'. > > How I do it in the PosgreSQL ? Postgres does not allow for queries to reference more than one database. In 7.3 (currently in beta), you may be able to use schemas to keep your data in the same database and use queries referencing tables in different schemas. Whether or not this will work for you depends on why you have put the data into different databases. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SELECT with INNER data bases
On Thu, Oct 10, 2002 at 09:37:49AM -0300, 2000 Informática wrote: > Hi, > > I have two data bases: db1 and db2. > In MS SQL Server I can use > 'SELECT T1.ID, T2.NAME FROM db1..table1 T1 INNER JOIN db2..table2 T2 > ON T1.ID = T2.ID ...'. > > How I do it in the PosgreSQL ? > > HELP ME, PLEASE ! > > Sorry, > I'm a Brazilian and I don't speak and write English very well ! PostgreSQL doesn't allow this by default. You can use 7.3 and schemas as as suggested, or use a loadable module called dblink with 7.2. dblink is under the PostgreSQL contrib/ directory. See its documentation. And here goes the same as above, but in Portuguese :) O PostgreSQL não permite isso por padrão. Você pode usar 7.3 e schemas como foi sugerido, ou usar um módulo chamado dblink com 7.2. Esse módulo está no diretório contrib do PostgreSQL. Veja sua documentação. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + But what can you do with it? -- ubiquitous cry from Linux-user partner ---(end of broadcast)--- TIP 3: 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] NOTICE: generated by sequence nextval()
Postgresql 7.1.3-2
Red Hat 7.2
I've noticed that if I create a sequence, and then
do a select on it using nextval() everything works fine. However, if I drop the
sequence and then create it again, the same select statement generates a
NOTICE.
Is this normal behaviour or a bug? See below.
test=> create sequence
serial;CREATEtest=> select
nextval('serial'); nextval-
1(1 row)
test=> drop sequence
serial;DROPtest=> create sequence serial;CREATEtest=>
select nextval('serial');NOTICE: serial.nextval: sequence was
re-created nextval-
1(1 row)
[SQL]
I have a period of consultation with Fecha1 and Fecha2 and two tables with the follow data: Table1 |Clave_activo | Cantidad | fecha | | | | | | clave1 |5 | 01/02/00 | | clave3 |7 | 05/05/01 | | clave1 |2 | 03/09/02 | | clave2 |2 | 27/01/01 | | clave1 |1 | 03/11/02 | | clave3 |1 | 25/08/01 | | clave2 |3 | 27/01/00 | | clave1 |8 | 03/12/01 | | clave2 |2 | 27/01/02 | | clave1 |1 | 03/12/00 | | clave3 |2 | 25/08/02 | |.. | .. |..| Y Table2 |Clave_activo | nombre | | | | | clave1 | Product1 | | clave3 | Product3 | | clave3 | Product3 | | clave2 | Product2 | | clave1 | Product1 | |.. | .. | I need the SQL Instruction to obtain something like that: (Sum Product by year) | Nombre |yearFecha1|00|01|02|YearFecha2 |Total| | Product1 | .. | 6| 8| 3| ...| 17 | | Product2 | .. | 3| 2| 2| ...| 07 | | Product3 | .. | 7| 1| 2| ...| 10 | For my it is very important their help then has one week to finish my reports and east already has me a mess... If somebody wanted to help me with the other consultations, please tell me for private, be not that it bothers, my consultations to the other partners... _ Do You Yahoo!? La emoción e intensidad del deporte en Yahoo! Deportes. http://deportes.yahoo.com.mx ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
