[SQL] Consulta!
Hola a todos!!... El día 22-07-2004 Gerardo Castillo escribió lo siguiente: / Hello, I'm using PostgreSQL 7.4 I have a function wich use temporary tables. I read about temporary tables and they exists during the session. But i have to call this function many times in the same sesion with diferents parameters and expecting different results. So, there is a problem because the temporary table already exists during the second execution of the funcition. To avoid this, I used this sintax after de create table statement "ON COMMIT DROP" which destroy the table in the next commit. for example, If i run this script many times in the same session there weren't problems: begin; create temporary table test(x integer) ON COMMIT DROP; INSERT INTO test values(1); select * from test; commit; Then I tried to use this in function: CREATE OR REPLACE FUNCTION "public"."f_test" () RETURNS SETOF "pg_catalog"."record" AS'BEGIN CREATE TEMPORARY TABLE test( x integer ) ON COMMIT DROP; INSERT INTO test values (1); --RETORNA LOS RESULTADOS FOR res IN SELECT x FROM test LOOP RETURN NEXT res; END LOOP; RETURN;END;'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY DEFINER; and then I executed the function this way: BEGIN; SELECT * FROM f_test() AS R(x INTEGER); COMMIT; but in the second execution, it falis with an error wich said that doesn't exist the relation with OID ... I supose it is because the table doesn't exist because in the second execution the function couldn't create the table or it is using an old reference of the dropped table. I think if I put the begin and the commit inside the function, it will work. I tried this way, but it doesn't compile: CREATE OR REPLACE FUNCTION "public"."f_test" () RETURNS SETOF "pg_catalog"."record" AS'BEGIN BEGIN; CREATE TEMPORARY TABLE test( x integer ) ON COMMIT DROP; . I tried too with START, but without success. I'd appeciate some help. Tanks, Gerardo. */ Yo tengo el mismo problema y no lo pude solucionar tampoco con Execute como se sugiere en otro mail... Alguien sabe cómo puedo solucionar este problema? 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo Abrí tu cuenta aquí
Re: [SQL] Consulta!
On Tue, 2005-10-18 at 14:26 +, Adriana Marcela Aguirre wrote: > Hola a todos!!... Hi! :) That's a known postges 'problem', one which, I guess, every pg-newbie encounters, especialy if he/she came from other RDBMSes. (I personaly came from MSSQL). > CREATE OR REPLACE FUNCTION "public"."f_test" () RETURNS SETOF > "pg_catalog"."record" AS' > BEGIN > CREATE TEMPORARY TABLE test( x integer ) ON COMMIT DROP; > > INSERT INTO test values (1); > > --RETORNA LOS RESULTADOS > FOR res IN SELECT x FROM test LOOP > RETURN NEXT res; > END LOOP; > RETURN; > END; > 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY DEFINER; > Postgres parsed your function, and compilled it, when first run. When doing so it refferenced your 'test' temp table via the oid. You do drop the table at the end of the proc, but prepared query plan for that function still references the OID that temp table had when it did exists. So, you need to stop postgres to 'prepare' query plan for that table. You do so by EXECUTEing the desired query, in your case: FOR res IN EXECUTE SELECT x FROM test LOOP ... It is more convinient to add the SQL query to a variable, and then exec that variable, like so: _SQL := 'SELECT a, b FROM tmpTable WHERE colText=' || quote_literal(someParametar); FOR res IN EXECUTE _SQL LOOP ... Mike -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(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
Re: [SQL] What Am I Doing Wrong?
Ignore my request. The solution is: --- computeIfUptime() - select (EXTRACT(EPOCH from TIMESTAMP 'now') - EXTRACT(EPOCH from TIMESTAMP '2005-10-18 17:00:00'))::integer -Original Message- From: Lane Van Ingen [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 18, 2005 3:39 PM To: pgsql-sql@postgresql.org Subject: What Am I Doing Wrong? I have been trying to subtract two timestamps from each other to determine the difference between the two timestamps (current time and a past timestamp called 'updated_time') IN SECONDS, but no luck. The trouble I am having is getting the first timestamp, representing the current time, to translate to seconds; have tried different commands (now(), localtimestamp, current_timestamp, etc) What am I doing wrong? Sample statement and error message is below: -- select EXTRACT(EPOCH from TIMESTAMP current_timestamp) - EXTRACT(EPOCH from TIMESTAMP updated_time) ::integer ERROR: syntax error at or near "current_timestamp" at character 37 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] What Am I Doing Wrong?
I have been trying to subtract two timestamps from each other to determine the difference between the two timestamps (current time and a past timestamp called 'updated_time') IN SECONDS, but no luck. The trouble I am having is getting the first timestamp, representing the current time, to translate to seconds; have tried different commands (now(), localtimestamp, current_timestamp, etc) What am I doing wrong? Sample statement and error message is below: -- select EXTRACT(EPOCH from TIMESTAMP current_timestamp) - EXTRACT(EPOCH from TIMESTAMP updated_time) ::integer ERROR: syntax error at or near "current_timestamp" at character 37 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Problem while using start transaction ans commit;
Hi All, I have a small problem in using nested transactions while working on Postgres 8.0. Ex: I have a function A() which in turn calls functions b() and c() , if i want commit something in b or c. i have to use start transaction read write; -- set of sql statements and then say commit. commit; but this is not working it is showing the the following error near start transaction read write " ERROR: SPI_execute_plan failed executing query "start transaction read write": SPI_ERROR_TRANSACTION "; or if i use only commit with out the start transaction command it throwing the runtime error as " ERROR: SPI_execute_plan failed executing query "commit": SPI_ERROR_TRANSACTION "; Can any one send me an example of how to use the start transaction or how to commit an nested transaction. Thanks in advance. Thanks & Regards, Sri
[SQL] problems with array
PostgreSQL is 7.4.7. My first table CREATE TABLE tb_cat ( id INTEGER, desc text ); INSERT INTO tb_cat VALUES (10, 'cat10'); INSERT INTO tb_cat VALUES (20, 'cat20'); INSERT INTO tb_cat VALUES (30, 'cat30'); My second table CREATE TABLE tb_array( id INTEGER, cat INTEGER[] ); INSERT INTO tb_array VALUES(1, ARRAY [10, 20]); When I write my select SELECT * from tb_cat WHERE id IN (SELECT cat FROM tb_array WHERE id=1); the output is: ERROR: operator does not exist: integer = integer[] HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. Can anyone help me? thanks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL]
how can i do a query with 2 databases??
Re: [SQL] cast
I believe int8(n.udf4) will do the trick for you. - Original Message - From: "Judith Altamirano Figueroa" <[EMAIL PROTECTED]> To: Sent: Friday, October 14, 2005 12:08 PM Subject: [SQL] cast > Hello I have a query that ran in 7.0.2, but in 8.0.1 does not, the query > is the next: > > > > select n.factura, > n.venta_neta, > c.nombre_cli || ' ' || c.apellido_pat_cli || ' ' || coalesce > (c.apellido_mat_cli,''), > date(n.fecha_hora_factura), > o.nombre_oft > from nota_venta n, > clientes c, > oft_central o > where date(n.fecha_hora_factura) >= '2005-10-01' and > date(n.fecha_hora_factura) <= '2005-10-14' and > n.id_cliente = c.id_cliente and > cast(n.udf4 as integer) = o.id_oft_central and > n.factura is not null and > n.s_factura != 'T' > order by o.nombre_oft"; > > it aparently returns an error because the cast, but I need to do the > cast, does somebody knows how can I change the cast but with the same > result, thnks > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Query information needed
Dear all,
I have a table created with this specifications:
CREATE TABLE cdr (
calldate timestamp with time zone NOT NULL default now(),
clid varchar(80) NOT NULL default '',
src varchar(80) NOT NULL default '',
dst varchar(80) NOT NULL default '',
dcontext varchar(80) NOT NULL default '',
channel varchar(80) NOT NULL default '',
dstchannel varchar(80) NOT NULL default '',
lastapp varchar(80) NOT NULL default '',
lastdata varchar(80) NOT NULL default '',
duration bigint NOT NULL default '0',
billsec bigint NOT NULL default '0',
disposition varchar(45) NOT NULL default '',
amaflags bigint NOT NULL default '0',
accountcode varchar(20) NOT NULL default '',
uniqueid varchar(32) NOT NULL default '',
userfield varchar(255) NOT NULL default ''
);
I want to extract the number of calls placed in 1 hour and the average
call duration
I'm working with this query:
SELECT date_trunc('hour',calldate), duration FROM cdr WHERE src=601
ORDER BY calldate;
i tried several other queries but i'm not able to count the number of
calls in an hour (better in a time interval) and calculate the average
duration.
Any suggestions would be appreciated! Tnx
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] generating a sequence table against which to do a LEFT OUTER JOIN
Andrew Hammond <[EMAIL PROTECTED]> wrote:
> So I need an end result that has entries for all days, even when
> there's nothing happening on those days, generate from a timestamped
> event table. I've already got the interesting entries. Now I need to
> fill the holes.
>
> To do this, I'm thinking a LEFT OUTER JOIN against a date sequence
> table. So, how do I generate a table with every day from A to B?
>
> Or am I going about this the wrong way?
What you have is fine, but you're doing extra work. There's this neat
function in PostgreSQL 8.0 or better (you can write one for earlier
versions) called generate_series().
> SELECT pop_days('2005-01-01'::date, '2005-02-01'::date); -- barfs.
SELECT
'2005-01-01'::date + s.i * '1 day'::interval AS "Date",
t.your_date_col
FROM
generate_series(0,'2005-02-01'::date - '2005-01-01'::date - 1) AS s(i);
LEFT JOIN
your_table t
ON
('2005-01-01'::date + s.i = t.your_date_col);
You can also use generate_series() with a correllated subquery so as
not to have to hard-code dates.
HTH :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100mobile: +1 415 235 3778
When a man tells you that he got rich through hard work, ask him:
'Whose?'
Don Marquis, quoted in Edward Anthony, O Rare Don Marquis
---(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] Problem -Postgre sql
Hi I want to know substitute function for sql server ISNULL() function in postgre Regards,Vikas Jadhav Codec Communication Pvt. Ltd.Swargate, Pune.Ph: 020-2422 1460/70 (Ext 37)Email: [EMAIL PROTECTED]
[SQL] Very Urgent Req for SQL-DBS In Chennai
Hi All, We are having an opening for SQL-DBS for my CMM Level 5 Client in Chennai Skills : SQL-DBS Exp : 1+yrs Location : Chennai Kindly send us your updated resume [EMAIL PROTECTED] farzana@(at)shubhaminfo.(dot)com In case you are interested, kindly send us the following information at the earliest: 1. Your Current Location 2. Your contact number 3. Your current Organisation 4. Total IT Exp. 5. Total hands-on exp. in the relevant skills 8. Your current CTC 9. Expected CTC Regards Farha Shubham Infotech, Chennai Ph.: 24731286 or 52123617 WEB:WWW.Shubhaminfo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Double query (limit and offset)
List, I'm using the OFFSET / LIMIT combo in order to split up my query, so it only parses 20 rows at a time (for my php-scripted webpage). I'm using two queries; the first basically doing a select count(*) from [bla bla]; the second grabbing the actual data while setting LIMIT and OFFSET. In addition, I'm using the first query plus some calculations to parse total hits to the query, and number of pages etc etc. Now, my problem is this, the first query is simply counting all rows from the main table, whereas the second query has plenty of JOINS, and a GROUB BY statement - it's a fairly heavy query. The total (reported by the first query), it not at all the same as the amount of rows returned by the second query. I'd like to avoid having to run the "heavy" query twice, just in order to get the number of rows. Is there a smarter way of doing it ? Thanks, /mich ---(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
Re: [SQL] Problem while using start transaction ans commit;
Sri,
> I have a small problem in using nested transactions while
> working on Postgres 8.0.
This is a known problem with Postgres 8.0 - there is no support for
nested transactions (which occurs when calling functions). Your best bet
would be to raise an exception within B or C - this will cause a
rollback to wherever the exception is caught. If you surround the calls
to B and C in a block to catch the exception, this will provide
transaction-like semantics.
An example:
--
CREATE OR REPLACE FUNCTION tr_addcourse(employeeno, int4, coursename,
float4, text, timestamptz, int4)
RETURNS int4 AS
$BODY$-- Use case: 10.2.9: Add a course
DECLARE
transid int4;
cid int4;
errcode int4;
BEGIN
-- Setup default return code. This is used if we hit an
-- exception that we didn't throw.
SELECT -32767 into errcode; -- E_UNKNOWN
-- Start the transaction, lock tables
LOCK TABLE backend.courses IN ACCESS EXCLUSIVE MODE;
LOCK TABLE backend.courseareas IN SHARE MODE;
-- Access to administrators only
IF NOT tt_user_access(actor, 'a') THEN
SELECT -1 into errcode; -- Return E_NO_ACCESS
RAISE EXCEPTION 'User % does not have access.', actor;
END IF;
-- Check for a duplicate course name.
IF tt_course_name_active(cname) THEN
SELECT -2000 INTO errcode; -- E_DUP_COURSE
RAISE EXCEPTION 'Course "%" already exists.', cname;
END IF;
-- Check for course area status
SELECT tt_coursearea_status(carea) INTO errcode;
IF NOT errcode = 0 THEN -- NOT errcode = SUCCESS
RAISE EXCEPTION 'Error finding active course area %', carea;
END IF;
-- Grab a transaction ID
SELECT tt_acquire_transaction(actor, 'tr_addcourse') INTO transid;
IF transid < 0 THEN
SELECT transid into errcode; -- Return the error code.
RAISE EXCEPTION 'Could not acquire transaction.';
END IF;
-- Get the next course ID
SELECT nextval('backend.courses_courseid_seq') INTO cid;
-- Insert the row
INSERT INTO backend.courses
(transactionid, courseid, coursearea, coursename, active, duration,
description, contentdate, valid_months)
VALUES (transid, cid, carea, cname, TRUE, dur, desc, cdate,
valid_mths);
-- Success
RETURN cid;
EXCEPTION
WHEN RAISE_EXCEPTION THEN
RETURN errcode;
WHEN OTHERS THEN
RETURN -32767; -- E_UNKNOWN
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
--
In this code, whenever an exception is raised, the system will rollback
to the start of the block (BEGIN).
Best regards,
Ben Stewart
--
Robert Bosch (Australia) Pty. Ltd.
Engineering Quality Services, Student Software Engineer (RBAU/EQS4)
Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA
mailto:[EMAIL PROTECTED]
http://www.bosch.com.au/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Query information needed
On Oct 13, 2005, at 21:50 , [EMAIL PROTECTED] wrote:
Dear all,
I have a table created with this specifications:
CREATE TABLE cdr (
calldate timestamp with time zone NOT NULL default now(),
clid varchar(80) NOT NULL default '',
src varchar(80) NOT NULL default '',
dst varchar(80) NOT NULL default '',
dcontext varchar(80) NOT NULL default '',
channel varchar(80) NOT NULL default '',
dstchannel varchar(80) NOT NULL default '',
lastapp varchar(80) NOT NULL default '',
lastdata varchar(80) NOT NULL default '',
duration bigint NOT NULL default '0',
billsec bigint NOT NULL default '0',
disposition varchar(45) NOT NULL default '',
amaflags bigint NOT NULL default '0',
accountcode varchar(20) NOT NULL default '',
uniqueid varchar(32) NOT NULL default '',
userfield varchar(255) NOT NULL default ''
);
I want to extract the number of calls placed in 1 hour and the average
call duration
I'm working with this query:
SELECT date_trunc('hour',calldate), duration FROM cdr WHERE src=601
ORDER BY calldate;
i tried several other queries but i'm not able to count the number of
calls in an hour (better in a time interval) and calculate the average
duration.
For a first step, I'd build a view of
create view cdr_by_hour_view as
select date_trunc('hour,calldate) as callhour, duration
from cdr;
Then I'd select from this view using aggregates:
select callhour, avg(duration) as avg_duration
from cdr
where src = 601
group by callhour;
You could write it
This should help you with the simple case of just hourly averages.
For averages on any given interval I think it's a bit more involved
(as general cases generally are).
Hope this helps!
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Problem -Postgre sql
[It's PostgreSQL, Postgres, or pgsql. It's never "postgre"] On Oct 11, 2005, at 16:20 , Vikas J wrote: Hi I want to know substitute function for sql server ISNULL() function in postgre I'm not quite sure what the ISNULL() function does in SQL Server, but it sounds like it might be similar to either COALESCE or the IS NULL expression. These pages might help you: COALESCE http://www.postgresql.org/docs/8.0/interactive/functions- conditional.html#AEN12056 IS NULL http://www.postgresql.org/docs/8.0/interactive/functions-comparison.html Hope this helps. Michael Glaesemann grzm myrealbox com ---(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
Re: [SQL] What Am I Doing Wrong?
On Tue, Oct 18, 2005 at 03:38:54PM -0400, Lane Van Ingen wrote: > select EXTRACT(EPOCH from TIMESTAMP current_timestamp) - EXTRACT(EPOCH from > TIMESTAMP updated_time) ::integer > > ERROR: syntax error at or near "current_timestamp" at character 37 What are you intending "TIMESTAMP" to do? If you're trying to do a cast then see "Type Casts" in the documentation for the correct syntax: http://www.postgresql.org/docs/8.0/interactive/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS You might not need a cast at all: test=> SELECT extract(epoch FROM current_timestamp); date_part -- 1129678734.81522 (1 row) -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] query to file
Excuse me, how can I send a query to a file?, for example the result of this query: select id_articulo, articulo, color_code from articulos; ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] query to file
On Tue, Oct 18, 2005 at 06:03:59PM -0500, Judith Altamirano Figueroa wrote: > Excuse me, how can I send a query to a file?, for example the result of > this query: > > select id_articulo, articulo, color_code from articulos; If you have a shell on a platform that supports input/output redirection then you could redirect psql's output: psql -c 'select ...' > filename >From within psql you could use \g or \o; see the documentation for more information. http://www.postgresql.org/docs/8.0/interactive/app-psql.html If you're using some other client then consult its documentation. -- Michael Fuhr ---(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
Re: [SQL] query to file
On Oct 19, 2005, at 8:03 , Judith Altamirano Figueroa wrote: Excuse me, how can I send a query to a file?, for example the result of this query: select id_articulo, articulo, color_code from articulos; You don't mention what client you're using. In psql, you can use \o path/to/file.txt For more psql goodness, take a look at: http://www.postgresql.org/docs/8.0/interactive/app-psql.html Hope this helps! Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Problem -Postgre sql
On Tue, Oct 11, 2005 at 12:50:49PM +0530, Vikas J wrote: > I want to know substitute function for sql server ISNULL() function in postgre If you look in the index of the PostgreSQL documentation you'll find an entry for ISNULL: http://www.postgresql.org/docs/8.0/interactive/bookindex.html If PostgreSQL's ISNULL doesn't do what you want then please explain what you're trying to do. Many people on these lists don't use SQL Server so they don't know its ISNULL works. (BTW, it's PostgreSQL or Postgres, not Postgre.) -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL]
On Thu, 2005-10-13 at 10:31, Shavonne Marietta Wijesinghe wrote: > how can i do a query with 2 databases?? This is only supported by an add on called dblink, and it's a little bit klunky. Could schemas solve your problem? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] problems with array
Not sure if you got this figured out but I think SELECT * from tb_cat WHERE id IN (SELECT array_to_string(cat,',') as cat FROM tb_array WHERE id=1); is what your looking for? --- paperinik 100 <[EMAIL PROTECTED]> wrote: > PostgreSQL is 7.4.7. > > My first table > CREATE TABLE tb_cat ( > id INTEGER, > desc text > ); > INSERT INTO tb_cat VALUES (10, 'cat10'); > INSERT INTO tb_cat VALUES (20, 'cat20'); > INSERT INTO tb_cat VALUES (30, 'cat30'); > > My second table > CREATE TABLE tb_array( > id INTEGER, > cat INTEGER[] > ); > INSERT INTO tb_array VALUES(1, ARRAY [10, 20]); > > When I write my select > SELECT * from tb_cat WHERE id IN (SELECT cat FROM > tb_array WHERE id=1); > the output is: > ERROR: operator does not exist: integer = integer[] > HINT: No operator matches the given name and > argument type(s). You may need > to add explicit type casts. > > Can anyone help me? > thanks. > > > > ---(end of > broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] problems with array
On Tue, Oct 18, 2005 at 08:09:48PM -0700, Matthew Peter wrote: > Not sure if you got this figured out but I think > > SELECT * from tb_cat WHERE id IN (SELECT > array_to_string(cat,',') as cat FROM tb_array WHERE > id=1); > > is what your looking for? I doubt it, considering that it doesn't work :-( SELECT * from tb_cat WHERE id IN (SELECT array_to_string(cat,',') as cat FROM tb_array WHERE id=1); id | desc +-- (0 rows) This might do the trick: SELECT c.* FROM tb_cat AS c, tb_array AS a WHERE c.id = ANY (a.cat) AND a.id = 1; id | desc +--- 10 | cat10 20 | cat20 (2 rows) Or if you prefer the explicit join syntax: SELECT c.* FROM tb_cat AS c JOIN tb_array AS a ON c.id = ANY (a.cat) WHERE a.id = 1; id | desc +--- 10 | cat10 20 | cat20 (2 rows) -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Problem -Postgre sql
[Please do not email me directly. Please post to the list so others may help and benefit from the discussion.] On Oct 19, 2005, at 14:30 , Vikas J wrote: IsNull in sql server has syntax like isnull(column,substitute) if "column" is null it shows value of "substitute". That can be achieved with CASE clause in postrgre but I want alternate function. If you look at the doc links I provided below, you will find that COALESCE does exactly this. Can you tell me how to write function like MAX() that will work directly on colmuns. I want to create my own function that will not need table name as paramter. it shld work similarly to max() function in postgre. [Again, it's PostgreSQL or Postgres. It is *not* spelled "postgre".] In my experience, the max() aggregate function does not require table names as parameters and work on columns directly. http://www.postgresql.org/docs/8.0/interactive/functions-aggregate.html I suggest you take some time to look at the docs. They're quite extensive and helpful. http://www.postgresql.org/docs/8.0/interactive/index.html Michael Glaesemann grzm myrealbox com I'm not quite sure what the ISNULL() function does in SQL Server, but it sounds like it might be similar to either COALESCE or the IS NULL expression. These pages might help you: COALESCE http://www.postgresql.org/docs/8.0/interactive/functions- conditional.html#AEN12056 IS NULL http://www.postgresql.org/docs/8.0/interactive/functions- comparison.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] query to file
Judith Altamirano Figueroa wrote: Excuse me, how can I send a query to a file?, for example the result of this query: select id_articulo, articulo, color_code from articulos; if you mean psql \o filename and try to read output of a command \? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] problems with array
Ya, I didn't test it. The error message was expecting an integer not an array, so coverting it to a list crossed my mind 'assuming' the subselect 'could' return a string of integers for the IN clause. Oh well. I'm glad there's people like you test it. --- Michael Fuhr <[EMAIL PROTECTED]> wrote: > I doubt it, considering that it doesn't work :-( __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Problem -Postgre sql
Correct me if I am wrong, but isn't COALESCE standard in this way? Best Wishes, Chris Travers Metatron Technology Consulting Michael Glaesemann wrote: [Please do not email me directly. Please post to the list so others may help and benefit from the discussion.] On Oct 19, 2005, at 14:30 , Vikas J wrote: IsNull in sql server has syntax like isnull(column,substitute) if "column" is null it shows value of "substitute". That can be achieved with CASE clause in postrgre but I want alternate function. If you look at the doc links I provided below, you will find that COALESCE does exactly this. Can you tell me how to write function like MAX() that will work directly on colmuns. I want to create my own function that will not need table name as paramter. it shld work similarly to max() function in postgre. [Again, it's PostgreSQL or Postgres. It is *not* spelled "postgre".] In my experience, the max() aggregate function does not require table names as parameters and work on columns directly. http://www.postgresql.org/docs/8.0/interactive/functions-aggregate.html I suggest you take some time to look at the docs. They're quite extensive and helpful. http://www.postgresql.org/docs/8.0/interactive/index.html Michael Glaesemann grzm myrealbox com I'm not quite sure what the ISNULL() function does in SQL Server, but it sounds like it might be similar to either COALESCE or the IS NULL expression. These pages might help you: COALESCE http://www.postgresql.org/docs/8.0/interactive/functions- conditional.html#AEN12056 IS NULL http://www.postgresql.org/docs/8.0/interactive/functions- comparison.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
