[SQL] How to return a record set from function.
Dear Friends, I am newbie to Postgres. I am running 7.3.4 on Linux 7.3. I am using Pgadmin tool. I need to return the table rows via record set. Create table t1 (c1 int, c2 varchar, c3 varchar); Create or Replace function sel_t1 () returns setof records as ' select c1, c2, c3 from t1; ' Language SQL; It was fine and created a function. while i execute it as select sel_t1; I got the following error. ERROR: Cannot display a value of type RECORD How to solve this. Please help. Kumar
[SQL] Migrating Stored Procedures from MS SQL Server
Dear Friends, I am about to do a migration of stored procedures from MS SQL Server to Postgres. What is there any free tools available for it? Shall I write a SQL function or PL/pgSQL function for Stored Procedures? Please advise me on this. Thanks in advance. Kumar
[SQL] Unnamed Cursor return
Dear Friends, Using Postgres 7.3.4 on Linux 7.3 Server. Using PgAdmin II for Windows version 1.6.0 to connect to the server from my client machine. CREATE FUNCTION selfn() RETURNS refcursor AS 'DECLARE ref1 refcursor;BEGINOPEN ref1 FOR SELECT * FROM address;RETURN ref1;END;' LANGUAGE 'plpgsql' VOLATILE; this works fine. I execute it at PgAdmin SELECT selfn(); an unnamed cursor is returned. fetch all from ""; at PgAdmin shows a popup window saying the query is executed, but the content is not shown. at command prompt SELECT selfn(); an unnamed cursor is returned. fetch all from ""; WARNING: PerformPortalFetch: portal "" not foundFETCH 0 (1) pls have a look in the function, that I have named the cursor as ref1, but again it is returning a unnamed cursor? (2) How to fetch the content of the unnamed cursor at PgAdmin and at Command prompt? Please help me with this, as it is much helpful for my development purpose. Regards Kumar
[SQL] Fw: Change column data type
Dear Friends, Using Postgres 7.3.4 over the linux server 7.3. Is it possible to alter/change the data type of a existing table's column, with out dropping and recreating a column of same name. Thanks for ur knowledge sharing. Regards Kumar
[SQL] Name the Parameters
Dear Gurus, Is it possible to name the IN parameters of the PLpgSQL Functions. Because naming the parameter will ease coding. See the following function for insert, having 10 parameters as a input. = CREATE FUNCTION InsertFn(int8, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, timestamp) RETURNS varchar AS 'BEGIN RETURN ''HAi'';-- Check if the address_id specified exist in the address tableIF NOT EXISTS ( SELECT address_id FROM address WHERE address_id = $1 AND rec_deleted_flag = ''N'' )THENRAISE EXCEPTION ''The Address Specified is Invalid'';END IF; -- Check if the timestamp is same for the given address_idIF NOT EXISTS ( SELECT address_id FROM "WATS".address WHERE address_id = $1 AND rec_modified_date = $12; )THENRAISE EXCEPTION ''The record has already been updated by another user.'';END IF; -- Else, update the recordUPDATE "WATS".addressSET address = $2 , city = $3 , state = $4 , country = $5 , zipcode = $6 , email = $7 , home_phone = $8 , work_phone = $9 , cell_phone = $10 , pager = $11WHERE address_id = $1;END;' LANGUAGE 'plpgsql' IMMUTABLE;GRANT EXECUTE ON FUNCTION sp_upd_add_001(int8, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, timestamp) TO PUBLIC;GRANT EXECUTE ON FUNCTION "WATS".sp_upd_add_001(int8, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, varchar, timestamp) TO wats;= See it is difficult to pass the parameters with out name them. (2) I am getting error at the code IF NOT EXISTS... Is that not supported at Postgres? Please shed ur light on this. pls Regards Kumar
[SQL] Automated Backup
Dear Gurus, Is there a way to automate the backup databases using pg_dump (like in SQL server)? Regards Kumar
[SQL] Datafiles for Databases
Dear Friends, I was looking for a structure like oracle or ms Sql server in Postgres. What I have expected is individual datafiles for individual databases. But i cant fine such items in the /usr/local/pgsql/data directory. Will the postgres create individual data file for databases? How to get the datafile path of a database? Please shed light. Regards Kumar
[SQL] Problem with timestamp - Pls help
Dear Friends, I am using Postgres 7.3.4 on Linux server 7.3. I wanted to update one column of my table with now() or timestamp. And I want that timestamp of format 2003-09-19 18:39:08.13 To achieve this I have used the following wats=# select now(); now-- 2003-09-19 18:39:58.62398+00(1 row) wats=# select substr(now(),1,22); substr 2003-09-19 18:40:01.25(1 row) wats=# select timestamp(substr(now(),1,22));ERROR: parser: parse error at or near "substr" at character 18wats=# select to_timestamp(substr(now(),1,22));ERROR: Function to_timestamp(text) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecastswats=# select date(substr(now(),1,22)); date 2003-09-19(1 row) Also I cant directly update my timestamp column as follows update "WATS".users set to_rec_modified_date = substr(now(),1,22); ERROR: column "rec_modified_date" is of type timestamp without time zone but _expression_ is of type text You will need to rewrite or cast the _expression_ Please help me with this. Regards Senthil Kumar S
[SQL] Backup error - Pls help
Hi, I have 2 versions of Postgres server 7.2 & 7.3.4 installed on Linux server 7.3. I have not started the Postgres Server 7.2 which is located at /var/library/pgsql. I am running and using only 7.3.4 located at /usr/local/pgsql. After I login, I come to the /usr/local/pgsql/bin and then issued the following [EMAIL PROTECTED] bin]$ pg_dump -h 192.168.2.51 -f /home/usr1/prod.sql prodpg_dump: server version: pg_dump.mo; pg_dump version: 7.2.1pg_dump: aborting because of version mismatch (Use the -i option to proceed anyway.)[EMAIL PROTECTED] bin]$ pg_dump -h 192.168.2.51 -i -f /home/usr1/prod.sql prod pg_dump: server version: pg_dump.mo; pg_dump version: 7.2.1pg_dump: proceeding despite version mismatchpg_dump: query to obtain list of data types failed: ERROR: Attribute "typprtlen" not found[EMAIL PROTECTED] bin]$ Where i am doing mistake. Pls shed some light. Thank you very much. Kumar
Re: [SQL] Backup error - Pls help
While I issue which pg_dump it is showed me /usr/local/pg_dump But how to run the backup utility pg_dump to backup my database. - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Kumar" <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> Sent: Monday, September 22, 2003 3:20 PM Subject: Re: [SQL] Backup error - Pls help > On Monday 22 September 2003 10:01, Kumar wrote: > > Hi, > > > > I have 2 versions of Postgres server 7.2 & 7.3.4 installed on Linux server > > 7.3. I have not started the Postgres Server 7.2 which is located at > > /var/library/pgsql. > > RedHat 7.3 I guess you mean? > > > I am running and using only 7.3.4 located at /usr/local/pgsql. > > > > After I login, I come to the /usr/local/pgsql/bin and then issued the > > following > > > > > > [EMAIL PROTECTED] bin]$ pg_dump -h 192.168.2.51 -f /home/usr1/prod.sql prod > > pg_dump: server version: pg_dump.mo; pg_dump version: 7.2.1 > > pg_dump: aborting because of version mismatch (Use the -i option to > > proceed anyway.) [EMAIL PROTECTED] bin]$ pg_dump -h 192.168.2.51 -i -f > > /home/usr1/prod.sql prod > > What directory you are in has nothing to do with which copy of a program you > will run. There is a utility called "which" to help you: "which pg_dump". > Almost certainly what you want to do is: > /usr/local/pgsql/bin/pg_dump -h ... > > You probably want to get a book on unix/linux and read up about the various > search paths. > -- > Richard Huxton > Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Fw: [SQL] Backup error - Pls help
Dear Friend, I understood what you are saying. But could you pls tell me how to change the path of the pg_dump so that it should point to /usr/local/pgsql/bin/pg_dump Please help - Original Message - From: "Kumar" <[EMAIL PROTECTED]> To: "Richard Huxton" <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> Sent: Monday, September 22, 2003 5:16 PM Subject: Re: [SQL] Backup error - Pls help > While I issue > which pg_dump it is showed me > /usr/local/pg_dump > > But how to run the backup utility pg_dump to backup my database. > > - Original Message - > From: "Richard Huxton" <[EMAIL PROTECTED]> > To: "Kumar" <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> > Sent: Monday, September 22, 2003 3:20 PM > Subject: Re: [SQL] Backup error - Pls help > > > > On Monday 22 September 2003 10:01, Kumar wrote: > > > Hi, > > > > > > I have 2 versions of Postgres server 7.2 & 7.3.4 installed on Linux > server > > > 7.3. I have not started the Postgres Server 7.2 which is located at > > > /var/library/pgsql. > > > > RedHat 7.3 I guess you mean? > > > > > I am running and using only 7.3.4 located at /usr/local/pgsql. > > > > > > After I login, I come to the /usr/local/pgsql/bin and then issued the > > > following > > > > > > > > > [EMAIL PROTECTED] bin]$ pg_dump -h 192.168.2.51 -f /home/usr1/prod.sql prod > > > pg_dump: server version: pg_dump.mo; pg_dump version: 7.2.1 > > > pg_dump: aborting because of version mismatch (Use the -i option to > > > proceed anyway.) [EMAIL PROTECTED] bin]$ pg_dump -h 192.168.2.51 -i -f > > > /home/usr1/prod.sql prod > > > > What directory you are in has nothing to do with which copy of a program > you > > will run. There is a utility called "which" to help you: "which pg_dump". > > Almost certainly what you want to do is: > > /usr/local/pgsql/bin/pg_dump -h ... > > > > You probably want to get a book on unix/linux and read up about the > various > > search paths. > > -- > > Richard Huxton > > Archonet Ltd > ---(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] Compatible datatype of image in SQL server
Dear Friends, Can anyone suggest me the compatible datatype in Postgres 7.3.4 running on RH Linux 7.2 for 'image' datatype in SQL Server Thanks for help. kumar
[SQL] Large Objects and Bytea
Hi Friends, I am running Postgres 7.3.4 on RH Linux 7.2. I am migrating MS SQL DB to Postgres DB. I have tables with columns of data type 'Image' in the MS SQL database. IF I choose 'bytea' datatype, I am afraid it may lead to poor performance of the database (which I read from the manual). In this case what is the best data type to use. Please suggest me. The explanation of using LOB objects in a table and to write them with image is not very clear in the documentation. Can any one send me any link or white paper or examples about it. Thanks in advance. Kumar
[SQL] How to generate object DDL of the database objects
Dear Friends, Any body could pls share their idea on creating object DDL for the postgres data objects from the Postgres Server 7.3.4 running on RH Linux 7.2. Regards Kumar
[SQL] Converting Query from MS SQL
Dear Friends,
I am working with Postgres 7.3.4 on RH Linux 7.2. While
migrating all the SPs (from MS SQL Server), I come across these lines in MS SQL
Server procedure.
SET @v_sql = "UPDATE "Schema1".employee SET
rec_deleted_flag = 'Y' WHERE empid IN (" + @p_list_ids +
");" EXEC(@v_sql)
Actually to dynamically pass the values for the 'IN' the pass
the @p_list_ids (a comma separated string ) to the variable @v_sql and
then execute it.
How can I change it for postgres?
Thank you very much for ur support
Regards
Kumar
[SQL] Problem with Escape charactor
Dear Friends,
I am working with Postgres 7.3.4 on RH Linux 7.2 . I am
executing a dynamic query inside a PL/pgSQL procedure and I am having the
following problem.
While a line in the PL/pgSQL function is like the
following
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = 'Y' WHERE action_plan_id
IN ('|| p_action_plan_ids || ')';
I got the following error.
ERROR: parser: parse error at or
near "Y" at character 68
While
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = '|| 'Y' ||' WHERE
action_plan_id IN ('|| p_action_plan_ids || ')';
Error is
ERROR: Attribute "y" not
found
While
EXECUTE 'update
"WATS".action_plan_master set rec_deleted_flag = '|| \'Y\' ||' WHERE
action_plan_id IN ('|| p_action_plan_ids || ')';
Error is
WARNING: plpgsql: ERROR during
compile of sp_del_met_001 near line 47 ERROR:
unterminated string
How can I specify a string charactor, as the PgAdmin3 is not
using double quotes for Strings. Anyone pls shed some light.
Regards
Kumar
[SQL] Possible to have array as input paramter for a function?
Dear Friends, I am working with Postgres 7.3.4 on RH Linux 7.2 and my windows client is PgAdmin 3. Is it possible to pass a Varchar[] as a input parameter for a PL/pgSQL function. While I tried it give a error Type "varchar[]" does not exists. Does this is supported in Postgres? Anyone have a link or while paper on handling arrays in functions? Please enlighten me on this. Regards Kumar
[SQL] Object description at Client Window
Dear Friends, I am working with Postgres 7.3.4 on RH linux 7.2. I could get into the command prompt to describe a table structure. Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit training=# \d emp Table "emp" Column | Type | Modifiers+---+--- no | integer | name | character varying(20) | age | integer | training=# But I wanted to know whether this description could be availed at the command prompt. I am using PgAdmin3. I have checked the systems tables also. pg_tables can tell us only the table and the columns inside tables. Any idea to share with me, please. I am looking for something like sp_helptext in MS SQL server. Regards Kumar
Re: [SQL] Object description at Client Window
Hi , Jordan, thanks for ur reply. But I am not asking that. I want to get all the column names of any table at the PgAdmin3 SQL Window. To make it more clear, actually i wanted to send the table name as the input parameter for a function and expecting the column names, data types, etc as the output. Is there any command or any system table from that I could query the column names of a table (other than \d table name at the command prompt). Kumar - Original Message - From: Jordan S. Jones To: Kumar Cc: [EMAIL PROTECTED] Sent: Friday, October 17, 2003 11:50 AM Subject: Re: [SQL] Object description at Client Window give psql -E a try.. It will display any internal SQL commands that it uses.Jordan S. JonesKumar wrote: Dear Friends, I am working with Postgres 7.3.4 on RH linux 7.2. I could get into the command prompt to describe a table structure. Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit training=# \d emp Table "emp" Column | Type | Modifiers+---+--- no | integer | name | character varying(20) | age | integer | training=# But I wanted to know whether this description could be availed at the command prompt. I am using PgAdmin3. I have checked the systems tables also. pg_tables can tell us only the table and the columns inside tables. Any idea to share with me, please. I am looking for something like sp_helptext in MS SQL server. Regards Kumar-- I am nothing but a poor boy. Please Donate.. https://www.paypal.com/xclick/business=list%40racistnames.com&no_note=1&tax=0¤cy_code=USD
Re: [SQL] Object description at Client Window
I am sorry. Yes it worked. Thank you very much Mr. Jordan and Mr. Richard. - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Kumar" <[EMAIL PROTECTED]>; "Jordan S. Jones" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, October 17, 2003 2:54 PM Subject: Re: [SQL] Object description at Client Window > On Friday 17 October 2003 09:44, Kumar wrote: > > Hi , > > > > Jordan, thanks for ur reply. But I am not asking that. > > > > I want to get all the column names of any table at the PgAdmin3 SQL Window. > > To make it more clear, actually i wanted to send the table name as the > > input parameter for a function and expecting the column names, data types, > > etc as the output. > > > > Is there any command or any system table from that I could query the column > > names of a table (other than \d table name at the command prompt). > > Try what the man said. Start psql with -E and issue \d mytable and it will > show you the SQL it uses to produce the table's details. > > -- > Richard Huxton > Archonet Ltd ---(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
Re: [SQL] Object description at Client Window
But I have get into another problem. While I execute the following command I
could get the result as U can see below
etgsuite=# SELECT a.attname,format_type(a.atttypid, a.atttypmod),
a.attnotnull, a.atthasd
ef, a.attnum
FROM pg_class c, pg_attribute a
WHERE c.relname = 'companies'
AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum;
attname | format_type | attnotnull | atthasdef |
attnum
+-++---+
company_id | bigint | t | t |
1
name | character varying(100) | f | f |
2
website| character varying(50) | f | f |
3
address1 | character varying(100) | f | f |
4
address2 | character varying(100) | f | f |
5
city | character varying(50) | f | f |
6
state | character varying(50) | t | f |
7
postal_code| character varying(30) | t | f |
8
country| character varying(50) | t | f |
9
account_manager_id | bigint | t | f |
10
primary_contact_id | bigint | t | f |
11
company_type_id| bigint | t | f |
12
status_flag| bigint | f | f |
13
lead_source| bigint | f | f |
14
lead_date | timestamp without time zone | f | f |
15
industry_type | bigint | f | f |
16
rec_modifier_id| bigint | t | f |
17
rec_created_date | timestamp without time zone | t | f |
18
rec_modified_date | timestamp without time zone | f | f |
19
rec_deleted_flag | character(1)| t | f |
20
(20 rows)
So I tried to create a plpgsql function as follows to return these for all
the table name. So I have created a function like this
CREATE OR REPLACE FUNCTION public.desc_table(varchar)
RETURNS refcursor AS
'DECLARE
ref REFCURSOR ;
p_tablename ALIAS FOR $1;
BEGIN
OPEN ref FOR
SELECT a.attname,
format_type(a.atttypid, a.atttypmod),
a.attnotnull,
a.atthasdef,
a.attnum
FROM pg_class c, pg_attribute a
WHERE c.relname = p_tablename
AND a.attnum > 0
AND a.attrelid = c.oid
ORDER BY a.attnum;
RETURN ref;
END;'
LANGUAGE 'plpgsql' VOLATILE;
While trying to execute this
select desc_table('companies');
I got the following error.
WARNING: Error occurred while executing PL/pgSQL function desc_table
WARNING: line 7 at open
ERROR: Unable to identify an operator '=' for types 'name' and 'character
varying'
You will have to retype this query using an explicit cast
I have write many functions of the same structure and executed with out
problems. Where I am doing wrong here.
Please shed some light.
Regards
Kumar
----- Original Message -
From: "Richard Huxton" <[EMAIL PROTECTED]>
To: "Kumar" <[EMAIL PROTECTED]>; "Jordan S. Jones" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, October 17, 2003 2:54 PM
Subject: Re: [SQL] Object description at Client Window
> On Friday 17 October 2003 09:44, Kumar wrote:
> > Hi ,
> >
> > Jordan, thanks for ur reply. But I am not asking that.
> >
> > I want to get all the column names of any table at the PgAdmin3 SQL
Window.
> > To make it more clear, actually i wanted to send the table name as the
> > input parameter for a function and expecting the column names, data
types,
> > etc as the output.
> >
> > Is there any command or any system table from that I could query the
column
> > names of a table (other than \d table name at the command prompt).
>
> Try what the man said. Start psql with -E and issue \d mytable and it will
> show you the SQL it uses to produce the table's details.
>
> --
> Richard Huxton
> Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Max input parameter for a function
Dear friends, While trying to allocate about 36 input parameters, I got an error saying that the max input parameter for a function is only 32. Is it right? How to overcome this? Because I wanna insert records into a table of 55 columns with a lot of NULL able columns. I am using Postgres 7.3.4 on RH Linux 7.2. Note: I can't use 2 functions , one to insert 32 rows into the table first and the update the remaining columns with other function. Because there are only 27 columns that are not null. Please shed some light. Regards Kumar
[SQL] Unable to user pg_restore
Dear Friends, I have created a compressed backup for the database - sampledb using the following $ pg_dump -h 192.128.2.51 -v -u -f /home/db_repository/sampledb20031020.sql.tar.gz wats -F c while I try to restore this with the pg_restore function as follows $ pg_restore -d sampledbtest -f /home/db_repositorysampledb.sql.tar.gz -F c -v -c -O -h 192.128.2.51 -p 5432 -uUser name: postgres It never prompt for password, so I just typed the password , and get the error as follows, postgrespg_restore: [archiver] did not find magic string in file header$ Anyone could advise me on this pls. Kumar
[SQL] Scripting only the functions
Dear Friends, I am working with Postgres 7.3.4 on RH Linux server 7.2. Using pg_dump I could manage to take a script for all the DB objects. But wanted to take the script (DDL) for all the scripts in my database. While I searched I dont find any options in the pg_dump except for script tables only. Is there a way? Thanks Kumar
[SQL] Fw: Error message during compressed backup
Dear Friends, While doing compressed backup for one of the database running at Postgres Server 7.3.4 on RH Linux 7.2, I got the following error., but it backup other items --Command to backup $ $ pg_dump -h 192.xxx.x.xxx -p 5432 -v testdb -f /home/db_repository/testdb20031023.sql.tar.gz -u -F c --Error msg WARNING: owner of function "plpgsql_call_handler" appears to be invalid Could anyone tell me why I am getting this. I could able to do normal back and restore. Please shed some light. Regards Kumar
[SQL] Using % type in Parameters
Dear Friends, Clarification about the support for the usage of % TYPE in Postgres. I am working with postgres 7.3.4 on RH Linux 7.2. Can I use the %TYPE in parameters as in Oracle. CREATE OR REPLACE FUNCTION public.fn_listtitle(varchar, varchar) RETURNS refcursor AS' DECLARE ref REFCURSOR;BEGINOPEN ref FOR SELECT "Lookuptype", "Lookupname" FROM "Lookups" WHERE "Lookuptype" = $1 AND "Lookup_ID " = $2;END;' LANGUAGE 'plpgsql' VOLATILE; Here can I use as CREATE OR REPLACE FUNCTION public.fn_listtitle(%Lookups.Lookuptype, %Lookups.Lookup_ID) Please shed some light. Regards Kumar
Re: [SQL] Error message during compressed backup
Hi Peter Eisentraut, >>select proowner from pg_proc where proname = 'plpgsql_call_handler'; It gives me an id '101' While I search for the users in the pg_user, there is no user of id 101 select * from pg_user where usesysid = 101; No result was fetched. While I search this way select * from pg_user where usename like 'postgres' This give me a result username = postgres usesysid = 1 In comments you said >> You may want to adjust the owner of the function to a valid user (use UPDATE). Do u mean I need to update the table pg_proc, with the following statement UPDATE pg_proc SET proowner = 1 where proname = 'plpgsql_call_handler'; I am asking this because to ensure not any wrong impact gets into the live database. Pls advise. Regards Kumar - Original Message - From: "Peter Eisentraut" <[EMAIL PROTECTED]> To: "Senthil Kumar S" <[EMAIL PROTECTED]> Cc: "psql" <[EMAIL PROTECTED]> Sent: Friday, October 24, 2003 6:01 PM Subject: Re: [SQL] Error message during compressed backup > Senthil Kumar S writes: > > > $ $ pg_dump -h 192.xxx.x.xxx -p 5432 -v testdb -f /home/db_repository/testdb20031023.sql.tar.gz -u -F c > > > WARNING: owner of function "plpgsql_call_handler" appears to be invalid > > Run > > select proowner from pg_proc where proname = 'plpgsql_call_handler'; > > which gives you the ID of the user that owns this function. Then run > > select * from pg_user; > > to get the list of valid users. You may want to adjust the owner of the > function to a valid user (use UPDATE). > > -- > Peter Eisentraut [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Using UNION inside a cursor
Dear Friends,
I am working on Postgresql 7.3.4 on RH Linux Server 7.3.
I have problem in executing the following
procedure
CREATE OR REPLACE FUNCTION list_history()
RETURNS refcursor
AS'DECLARE ref REFCURSOR;BEGIN OPEN
ref FOR (SELECT * FROM history WHERE obs_type =
\'AA\' ) UNION (SELECT *
FROM history WHERE obs_type =
\'TA\');
RETURN ref;END;' LANGUAGE 'plpgsql'
VOLATILE;
While executing this I got the following error
WARNING: plpgsql: ERROR during compile of
list_history near line 5
ERROR: syntax error at "("
While I execute the following code it is working fine and
fetches values.
(SELECT * FROM history WHERE
obs_type = \'AA\' ) UNION (SELECT *
FROM history WHERE obs_type =
\'TA\');
Where I am wrong. Please shed some light,
Regards
Kumar
Re: [SQL] Using UNION inside a cursor
Hi Christoph Haller,
Thanks you very much. It worked.
- Original Message -
From: "Christoph Haller" <[EMAIL PROTECTED]>
To: ""Kumar"" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, October 30, 2003 3:56 PM
Subject: Re: [SQL] Using UNION inside a cursor
> >
> > Dear Friends,
> >
> > I am working on Postgresql 7.3.4 on RH Linux Server 7.3.=20
> >
> > I have problem in executing the following procedure
> >
> > CREATE OR REPLACE FUNCTION list_history()
> > RETURNS refcursor AS
> > 'DECLARE
> > ref REFCURSOR;
> > BEGIN
> > OPEN ref FOR
> > (SELECT * FROM history WHERE obs_type =3D \'AA\' )
> > UNION=20
> > (SELECT * FROM history WHERE obs_type =3D \'TA\');
> >
> > RETURN ref;
> > END;'
> > LANGUAGE 'plpgsql' VOLATILE;
> >
> > While executing this I got the following error
> >
> > WARNING: plpgsql: ERROR during compile of list_history near line 5
> > ERROR: syntax error at "("
> >
> > While I execute the following code it is working fine and fetches
values.
> > (SELECT * FROM history WHERE obs_type =3D \'AA\' )
> > UNION=20
> > (SELECT * FROM history WHERE obs_type =3D \'TA\');
> >
> > Where I am wrong. Please shed some light,
> >
> > Regards
> > Kumar
> >
> My suspicion is the plpgsql parser doesn't accept the opening
> parenthesis. What happens on
>
> OPEN ref FOR SELECT * FROM (
> (SELECT * FROM history WHERE obs_type =3D \'AA\' )
> UNION=20
> (SELECT * FROM history WHERE obs_type =3D \'TA\')
> ) ;
>
> 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] Using UNION inside a cursor
Hi Tom, I need the parentheses, because the where condition is different for the 1 and 2 nd query . Moreover my actual use includes a 'Limit' clause also. so the parentheses is needed. I have tried with execute but it showed error. But I have managed to fix the problem like this. OPEN ref FOR SELECT * FROM (SELECT * FROM history WHERE obs_type =3D \'AA\' ) UNION (SELECT * FROM history WHERE obs_type =3D \'TA\') as foo; Regards Kumar - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Christoph Haller" <[EMAIL PROTECTED]> Cc: ""Kumar"" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, October 30, 2003 8:50 PM Subject: Re: [SQL] Using UNION inside a cursor > Christoph Haller <[EMAIL PROTECTED]> writes: > >> OPEN ref FOR > >> (SELECT * FROM history WHERE obs_type =3D \'AA\' ) > >> UNION > >> (SELECT * FROM history WHERE obs_type =3D \'TA\'); > > > My suspicion is the plpgsql parser doesn't accept the opening > > parenthesis. > > Yeah. Looking at the plpgsql grammar, it expects the token after FOR to > be either SELECT or EXECUTE. This example demonstrates that that code > is wrong, so I'll fix it for 7.4. But in the meantime, why don't you > just leave out the parentheses? > > regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] pg_dump error - Permission denied
Dear friends,
I am working with Postgres 7.3.4 on RH Linux 7.3.
I could manage to take a backup using the following command
and as the user 'postgres'.
$ pg_dump -h 192.168.2.51 -v -u -f
/home/db_repository/test20031103.sql test
But while I try to execute the same command with the user
'kumar', it gives me the following error
>>
pg_dump: query to get data of sequence "clients_client_id_seq"
failed: ERROR: clients_client_id_seq: permission deniedpg_dump: ***
aborted because of error
But as the user 'kumar' I could manage to get the next value
of the sequence with the following command
select nextval('test.clients_client_id_seq');
Previously, user 'postgres' is the owner of the database and I
have changed that to 'kumar' via the following command
UPDATE pg_database SET datdba = 105 where datname =
'test';
Even though I am the owner of the sequence, why I am getting
this error. Anybody could shed some light, pls.
Regards
Kumar
[SQL] Dynamic Query for System functions - now()
Dear Friends, I am using PostgreSQL 7.3.4 Server on RH Linux 7.2. I am trying to generate a dynamic query to fetch the next month interval. select now()+ interval'1 month'; -- This is working fine. I wanna dynamically assign the interval number. i,e --> select now()+ interval'n month'; For this I wanted to write a dynamic query. EXECUTE 'select now()+ interval\'' || 3|| 'month\''; Error ERROR: parser: parse error at or near "'select now()+ interval\''" at character 9 Help help me with this. I wanted to use this query inside a PLpgSQL function. Regards Kumar
Re: [SQL] Dynamic Query for System functions - now()
Dear Christoph Haller, The code that u sent is not working test=> EXECUTE 'select now()+ interval\'' ||to_char(3,'9')|| 'month\''; ERROR: parser: parse error at or near "'select now()+ interval\''" at character 9 test=> Also I tried just to run the now() function with a dynamic query, I got the following error. Please correct me. test=> select now(); now --- 2003-11-10 17:06:36.783779+00 (1 row) test=> execute 'select now()'; ERROR: parser: parse error at or near "'select now()'" at character 9 test=> Regards kumar - Original Message - From: "Christoph Haller" <[EMAIL PROTECTED]> To: ""Kumar"" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, November 10, 2003 5:07 PM Subject: Re: [SQL] Dynamic Query for System functions - now() > > > > Dear Friends, > > > > I am using PostgreSQL 7.3.4 Server on RH Linux 7.2. I am trying to generate= > > a dynamic query to fetch the next month interval. > > > > select now()+ interval'1 month'; -- This is working fine. > > > > I wanna dynamically assign the interval number. i,e --> select now()+ inter= > > val'n month'; > > > > For this I wanted to write a dynamic query. > > EXECUTE 'select now()+ interval\'' || 3|| 'month\''; > > Error > > ERROR: parser: parse error at or near "'select now()+ interval\''" at = > > character 9 > > > > Help help me with this. I wanted to use this query inside a PLpgSQL functio= > > n. > > > > Regards > > Kumar > > > You'd probably want to use the to_char() function like this > EXECUTE 'select now()+ interval\'' ||to_char(3,'9')|| 'month\''; > > The || operator is the string concatination operator. > > Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] ::text problem
Hi , I am running postgres 7.3.4 on Linux 7.2. I wanna print 'Not Supplied' if 'c1' column of datatype - 'timestamp' is null. For that I am using case statement SELECT CASE WHEN c1 IS NULL THEN 'Record_Not_Found'ELSE c1 END as approval_date FROM t1; ERROR: Bad timestamp external representation 'Record_Not_Found' SELECT CASE WHEN c1 IS NULL THEN 'Record_Not_Found'::text ELSE c1 END as approval_date FROM t1; ERROR: CASE types 'timestamp without time zone' and 'text' not matched Where I am doing wrong? Please shed some light. Regards Senthil Kumar S DBA
Re: [SQL] ::text problem
Thanks a lot. It worked. - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Kumar" <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> Sent: Tuesday, November 25, 2003 9:44 PM Subject: Re: [SQL] ::text problem > On Tuesday 25 November 2003 12:37, Kumar wrote: > > > > SELECT > > CASE WHEN c1 IS NULL THEN 'Record_Not_Found'ELSE c1 END as > > approval_date FROM t1; > > ERROR: Bad timestamp external representation 'Record_Not_Found' > > > > > > SELECT > > CASE WHEN c1 IS NULL THEN 'Record_Not_Found'::text ELSE c1 END as > > approval_date FROM t1; > > ERROR: CASE types 'timestamp without time zone' and 'text' not matched > > Almost - you're on the right lines. Try: > SELECT > CASE > WHEN c1 IS NULL THEN 'Record_Not_Found'::text > ELSE c1::text > END as approval_date FROM t1; > > You need to make sure both options return type text. > > -- > Richard Huxton > Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Equivalent of Reverse() functions
Dear Friends, I am migrating an SQL Server 2000 database to Postgres 7.3.4 running on RH Linux 7.2. While migrating I encounter SQL Server REVERSE( ) function, seems no such functions at Postgres. Is there a equivalent function available at Postgres? Please shed some light Regards Kumar
[SQL] Concatenating multiple fetches into a single string
Dear Friends,
I am doing a migration from SQL server to Postgres SQL. A
simple select fetches the following names.
select full_name FROM project_members where
project_members.project_role_id in (' + @p_res_ids + ') ;
Let us say if the results are
full_name
---
David
Postgres
plpgsql
I need to send the out put as David,Postgres,Plsql -- one
string, concatenates the fetches from multiple rows. This was handled in SQL
server with a cursor.
Can this be handled only by sql manipulation or need to use
cursors. If i use cursor in postgres, what is the equivalent of MS SQL Statement
WHILE @@FETCH_STATUS = 0.
please shed some light pls.
Re: [SQL] Concatenating multiple fetches into a single string
Thanks for your reply.
But how to use this comma_aggregate( ) function to concatenate the fetched
columns values from a select statement. In my example my select stmt fetches
the following 3 rows. How can I use this function to concatenate them.
Select full_name FROM project_members where project_members.project_role_id
in ( ' x,y,z ') ;
full_name
---
David
Postgres
plpgsql
Expected return string is - 'David,Postgres,Plsql'
Regards
Kumar
- Original Message -
From: "Tomasz Myrta" <[EMAIL PROTECTED]>
To: "Kumar" <[EMAIL PROTECTED]>
Cc: "psql" <[EMAIL PROTECTED]>
Sent: Tuesday, December 02, 2003 1:31 AM
Subject: Re: [SQL] Concatenating multiple fetches into a single string
> Dnia 2003-12-01 13:01, Użytkownik Kumar napisał:
> > Dear Friends,
> >
> > I am doing a migration from SQL server to Postgres SQL. A simple select
> > fetches the following names.
> >
> > select full_name FROM project_members where
> > project_members.project_role_id in (' + @p_res_ids + ') ;
> >
> > Let us say if the results are
> >
> > full_name
> > ---
> > David
> > Postgres
> > plpgsql
> >
> > I need to send the out put as David,Postgres,Plsql -- one string,
> > concatenates the fetches from multiple rows. This was handled in SQL
> > server with a cursor.
>
> I can't find simpler function (if I remember well, there is such one),
> so there is my version of aggregate function you need:
>
>
> create or replace function comma_aggregate(varchar,varchar) returns
> varchar as '
> begin
> if length($1)>0 and length($2)>0 then
> return $1 || '', '' || $2;
> elsif length($2)>0 then
> return $2;
> end if;
> return $1;
> end;
> ' language 'plpgsql';
>
> drop aggregate comma(varchar) cascade;
> create aggregate comma (basetype=varchar, sfunc=comma_aggregate,
> stype=varchar, initcond='' );
>
> Regards,
> Tomasz Myrta
>
>
> ---(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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Problem with dynamic query
Dear Friends, I am using postgres 7.3.4 running on RH Linux 7.2. While writing dynamic SQL stmts, i have the following problem. Using cursor works fine. OPEN ref FOR EXECUTE \'SELECT comma(full_name) FROM (SELECT usr.full_name FROM project_members,users usr WHERE project_members.project_role_id IN\'||\'(\'|| p_res_ids||\') AND usr.usr_id = project_members.usr_id ORDER BY usr.full_name)foo\'; RETURN ref; While I try to assign the value into a local variable v_names , it is not working EXECUTE \'SELECT comma(full_name) INTO '||v_names||' FROM (SELECT usr.full_name FROM project_members,users usr WHERE project_members.project_role_id IN\'||\'(\'|| p_res_ids||\') AND usr.usr_id = project_members.usr_id ORDER BY usr.full_name)foo\'; RETURN v_names; Why is it so. Cant I use 'INTO [variable_name]' inside a dynamic query. Please shed some light. Regards Kumar
[SQL] Multiple DB servers on a single machine
Dear Friends, I am having an RH Linux 7.3 box which is already running an Postgres 7.3.4 server. I want to install Postgres 7.4 on the same machine. Is it possible to install different versions of Postgres in a single RH Linux 7.3 box and the postmaster up and running for both (7.3.4 & 7.4 database servers). Please shed some light with a white paper or an article to do it. Regards Kumar
[SQL] Virtual records
Dear Friends, I am working with Postgres 7.3.4 on RH Linux. Here is a need while developing an Calendar application. I have a scheduled meeting at every month. While opening the calendar next 2 month, I need to populate the records for it. I dont want to use a temporary table and populate the records, as this will slow down the function. Is there any method for that Please shed some light. Regards Kumar
[SQL] Calendar Scripts - Quite a complex one
Dear Friends, I am working on Postgres 7.3.4 on RH Linux . For our application, we are in a position to give calendar function (appointments and scheduling) with our application. Can somebody suggest me links or sample scripts for developing the calendar function. It is much similar to MS outlook. Please shed some light. Regards Kumar
Re: [SQL] Calendar Scripts - Quite a complex one
Hi, The complexity comes while scheduling the appointments. Let us say, I have scheduled so many meetings in my calendar of various schedules like daily, 3 days once, weekly, bi weekly. monthly, bi monthly, etc. While I open the calendar for end of this year (say Dec 2004), I need to show those meetings in my calendar, but I have data until Jan 2004. What is the best way to show it. Populating the records from Jan 2004 to Dec 2004 in the pgsql function and display it in the calendar, or just write a query to generate temporary records only for that Dec 2004 and not storing them at the database. Please shed some idea. Regards Kumar - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> To: "Peter Eisentraut" <[EMAIL PROTECTED]>; "Kumar" <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> Sent: Wednesday, January 07, 2004 3:43 AM Subject: Re: [SQL] Calendar Scripts - Quite a complex one Peter, > You can probably lift out the complete calendar functionality from an > existing groupware solution, say, www.egroupware.org. I'm not sure > whether it's practical to do the calendar things in the database, since > you will also need a significant amount of intelligence in the client > to display reasonable calendar graphics, for instance. But all of the appointments, holidays, etc can and should be stored in the database, and by using function programming one can automate generating all of the raw data for the calendar graphics. We do this with our legal calendaring app. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Calendar Scripts - Quite a complex one
Hi, yes yes. U understood in a very correct way, as i have 2 tables - appointments and recurrences. And we are not gonna use PHP. For future dates, I am not gonna populate, instead I am gonna check for the recurrences tables for ever appointments and based on the conditions, I am gonna say how many time that appointment recure in that month and the timestamp. To process that I have get all the appointment data and its recurrence pattern data into the cursor. Is there a way to get the records one by one from the cursor and calculate it patterns. CREATE OR REPLACE FUNCTION crm.fn_calendar_daily_activities(timestamp) RETURNS refcursor AS 'DECLARE cal_daily_date ALIAS FOR $1; ref REFCURSOR; BEGIN OPEN ref FOR SELECT RETURN ref; END;' LANGUAGE 'plpgsql' VOLATILE; How to open the cursor here so that I could check its recurrences pattern. Please shed some light. Regards kumar - Original Message - From: "Chris Travers" <[EMAIL PROTECTED]> To: "Kumar" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Peter Eisentraut" <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> Sent: Wednesday, January 07, 2004 1:19 PM Subject: Re: [SQL] Calendar Scripts - Quite a complex one > Hi all; > > If I understand Kumar's post correctly, he is having some question relating > to the issue of even recurrance. I would highly suggest reading the > ICalendar RFC (RFC 2445) as it has some interesting ideas on the subject. > HERMES (my app with appointment/calendar functionality) doesn't yet support > appointment recurrance, and I have not formalized my approach to this. > However, here is the general approach I have been looking at: > > 1: Have a separate table of recurrance rules (1:1 with appointments) or have > a recurrance datatype. > > 2: Build some functions to calculate dates and times when the appointment > would recurr. You can also have a "Recur Until" field so you can limit your > searches this way. > > 3: Use a view to find recurring appointments on any given day. > > This avoids a very nasty problem in the prepopulation approach-- that of a > cancelled recurring meeting. How do you cancel ALL appropriate instances of > the meeting while leaving those that occured in the past available for > records? > > Kumar-- if you are working with PHP, I would be happy to work with you in > this endevor so that the same functionality can exist in my open source > (GPL'd) application. I think that the source for this would likely be one > of those things that might be best LGPL'd if added to my app. > > Best Wishes, > Chris Travers > > - Original Message - > From: "Kumar" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]>; "Peter Eisentraut" <[EMAIL PROTECTED]>; "psql" > <[EMAIL PROTECTED]> > Sent: Wednesday, January 07, 2004 1:06 PM > Subject: Re: [SQL] Calendar Scripts - Quite a complex one > > > > Hi, > > > > The complexity comes while scheduling the appointments. Let us say, I have > > scheduled so many meetings in my calendar of various schedules like daily, > 3 > > days once, weekly, bi weekly. monthly, bi monthly, etc. > > > > While I open the calendar for end of this year (say Dec 2004), I need to > > show those meetings in my calendar, but I have data until Jan 2004. > > > > What is the best way to show it. Populating the records from Jan 2004 to > Dec > > 2004 in the pgsql function and display it in the calendar, or just write a > > query to generate temporary records only for that Dec 2004 and not storing > > them at the database. > > > > Please shed some idea. > > > > Regards > > Kumar > > > > - Original Message - > > From: "Josh Berkus" <[EMAIL PROTECTED]> > > To: "Peter Eisentraut" <[EMAIL PROTECTED]>; "Kumar" <[EMAIL PROTECTED]>; > > "psql" <[EMAIL PROTECTED]> > > Sent: Wednesday, January 07, 2004 3:43 AM > > Subject: Re: [SQL] Calendar Scripts - Quite a complex one > > > > > > Peter, > > > > > You can probably lift out the complete calendar functionality from an > > > existing groupware solution, say, www.egroupware.org. I'm not sure > > > whether it's practical to do the calendar things in the database, since > > > you will also need a significant amount of intelligence in the client > > > to display reasonable calendar graphics, for instance. > > > > But all of the appointments, holidays, etc can and should be stored in the > > database, and by using function programming one can automate generating > all > > of the raw data for the calendar graphics. We do this with our legal > > calendaring app. > > > > -- > > -Josh Berkus > > Aglio Database Solutions > > San Francisco > > > > > > ---(end of broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] how to show table structure?
use this
Last login: Fri Jan 9 16:54:14 from 192.168.2.117
[EMAIL PROTECTED] ssakkaravel]$ psql -E training test
* QUERY **
SELECT usesuper FROM pg_user WHERE usename = 'test'
**
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
training=# \d books
* QUERY **
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='books'
**
* QUERY **
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull,
a.atthasdef, a.attnu
m
FROM pg_class c, pg_attribute a
WHERE c.relname = 'books'
AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
**
* QUERY **
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = 'books' AND c.oid = d.adrelid AND d.adnum = 1
**
Table "books"
Column| Type | Modifiers
-+---+--
--
--
bid | integer | not null default
nextval('test.books_bid_seq'::text
)
bname | character varying(20) |
price | money |
publication | date |
- Original Message -
From: "scott.marlowe" <[EMAIL PROTECTED]>
To: "Bing Du" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, January 10, 2004 4:40 AM
Subject: Re: [SQL] how to show table structure?
> On Fri, 9 Jan 2004, Bing Du wrote:
>
> > Greetings,
> >
> > How can I see the layout of a table in PostgreSQL 7.4? I've checked
> > several books and on-line documents, but was not able to figure out how
> > PostgreSQL does 'describe ' like it's done in other databases.
>
> If in psql, use the \d commands (\? will show you all of them.
>
> However, if you've not got psql to do it, you can look through the
> information_schema for anything like that, like so:
>
> select * from information_schema.tables;
>
> and so on.
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Fetching a single column from a record returning function
Hi Friends, Postgres 7.3.4 on RH Linux 7.2 I am using a record variable inside a functions and fetch the result via the follow command select * from .fn_email(1) as (email_folder_id int4,email_folder_name varchar,descrip varchar,msgcount int8,unreadcount int8,size int8); Is it possible to fetch only one column (the 'msgcount') from the function. Because I am interested in SUM(msgcount). Please shed some light. Regards kumar
Re: [SQL] Fetching a single column from a record returning function
Yes it worked. Thanks - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Kumar" <[EMAIL PROTECTED]> Cc: "psql" <[EMAIL PROTECTED]> Sent: Tuesday, January 20, 2004 9:28 PM Subject: Re: [SQL] Fetching a single column from a record returning function > "Kumar" <[EMAIL PROTECTED]> writes: > > select * from fn_email(1) > > as (email_folder_id int4,email_folder_name varchar,descrip varchar,msgcount > > int8,unreadcount int8,size int8); > > > Is it possible to fetch only one column (the 'msgcount') from the function.= > > Because I am interested in SUM(msgcount). Please shed some light. > > select sum(msgcount) from fn_email(1) > as (email_folder_id int4,email_folder_name varchar,descrip varchar,msgcount > int8,unreadcount int8,size int8); > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SQL Query for Top Down fetching of childs
Dear Friends, Postgres 7.3.4 on RH Linux 7.2. I need a query to get the Childs of a parent (Top down analysis). Need to list all the departments(Childs) of a parent organization. The table structure is CREATE TABLE organization( entity_id int4, entity_name varchar(100), entity_type varchar(25), parent_entity_id int4,) WITH OIDS; A parent can have n number of Childs. So I need to list all the childs for a parent. For example I query the Division , then it lists it Childs # select * from organization where parent_entity_id = 3; entity_id | entity_name | entity_type | parent_entity_id---+-+-+-- 5 | HR | EngineeringTeam | 3 12 | PM | EngineeringTeam | 3 8 | Finance | Dept | 3 6 | Quality | Dept | 3 I need to drill down to the last level Engineering Team in this example. So I query entity_id 8 further, it gives me its Childs =# select * from organization where parent_entity_id = 8; entity_id | entity_name | entity_type | parent_entity_id---+-+-+-- 15 | Audit | Group | 8 16 | Mkt | Group | 8(2 rows) Again, I need to query the entity_id 15 to get its child =# select * from organization where parent_entity_id = 15; entity_id | entity_name | entity_type | parent_entity_id---+-+-+-- 17 | CA | EngineeringTeam | 15 18 | Comm | EngineeringTeam | 15 19 | EComm | EngineeringTeam | 15(3 rows) I have used the following query, but not useful. CREATE OR REPLACE FUNCTION.fn_get_all_organization(int4) RETURNS SETOF organization AS'DECLARE pi_entity_id ALIAS FOR $1; rec_result organization%ROWTYPE; rec_proc organization%ROWTYPE; v_patent_entity_id INT; BEGIN FOR rec_result IN SELECT entity_id, entity_name, entity_type, parent_entity_id FROM organization ben WHERE ben.parent_entity_id = pi_entity_id LOOP IF rec_result.entity_type = \'EngineeingTeam\' THEN RETURN NEXT rec_result; ELSE v_patent_entity_id := rec_result.entity_id; LOOP FOR rec_proc IN SELECT bse.entity_id, bse.entity_name, bse.entity_type, bse.parent_entity_id FROM organization bse WHERE bse.parent_entity_id= v_patent_entity_id LOOPIF rec_proc.entity_type = \'EngineeringTeam\' THEN RETURN NEXT rec_proc;ELSE v_patent_entity_id := rec_proc.entity_id; END IF; END LOOP; EXIT WHEN rec_proc.entity_type = \'EngineeringTeam\'; END LOOP; END IF; END LOOP;RETURN;END;' LANGUAGE 'plpgsql' VOLATILE; Anybody pls help me with this. I am first time writing these kind of function for TOP DOWN analysis. Please shed light. Regards Senthil Kumar S
Re: [SQL] SQL Query for Top Down fetching of childs
Thanks a lot Mr.Richard Huxton. It managed to find a similar one and modified to my need. It is working fine. Thanks a lot - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Kumar" <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> Sent: Thursday, January 29, 2004 3:57 PM Subject: Re: [SQL] SQL Query for Top Down fetching of childs > On Thursday 29 January 2004 06:11, Kumar wrote: > > Dear Friends, > > > > Postgres 7.3.4 on RH Linux 7.2. > > > > I need a query to get the Childs of a parent (Top down analysis). Need to > > list all the departments(Childs) of a parent organization. The table > > structure is > > Two places to look for examples: > 1. the contrib/tablefunc folder has an example of this sort of thing > 2. search the mailing list articles for CONNECT BY (oracle's name for this > sort of thing) or "tree" > > -- > Richard Huxton > Archonet Ltd > > ---(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 ---(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] Error in declaring %rowtype for recurrence
Dear Friends, Postgres 7.3.4 on RH Linux 7.2 I am having an table name recurrence under the schema crm. While I refer it as %rowtype I have error msgs. CREATE OR REPLACE FUNCTION crm.fn_rowtypetest() RETURNS varchar AS'DECLARE rec_recurrence crm.recurrences%rowtype;BEGINFOR rec_recurrence IN SELECT * FROM crm.recurrences LOOPRAISE NOTICE 'Hai';END LOOP; RETURN 'DONE';END;' LANGUAGE 'plpgsql' VOLATILE; while I execute select crm.fn_rowtypetest() WARNING: plpgsql: ERROR during compile of fn_rowtypetest near line 0 ERROR: cache lookup for type 0 of recurrences.pg.dropped.16 failed The same function works if I declare that via an record type CREATE OR REPLACE FUNCTION crm.fn_rowtypetest() RETURNS varchar AS'DECLARE rec_recurrence record; -- Note the change hereBEGINFOR rec_recurrence IN SELECT * FROM crm.recurrences LOOPRAISE NOTICE 'Hai';END LOOP; RETURN 'DONE';END;' LANGUAGE 'plpgsql' VOLATILE; Why is that so? Anyone can please clear me. Regards Kumar
Re: [SQL] Error in declaring %rowtype for recurrence
I am sorry. I didn't get you. Can u pls tell me more Thanks kumar - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Kumar" <[EMAIL PROTECTED]> Cc: "psql" <[EMAIL PROTECTED]> Sent: Tuesday, February 10, 2004 2:02 PM Subject: Re: [SQL] Error in declaring %rowtype for recurrence > "Kumar" <[EMAIL PROTECTED]> writes: > > WARNING: plpgsql: ERROR during compile of fn_rowtypetest near line 0 > > ERROR: cache lookup for type 0 of recurrences.pg.dropped.16...= > > . failed > > PG 7.4 is a little better at dealing with dropped columns in plpgsql > rowtypes. > > regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] How to avoid nulls while writing string for dynamic query
Dear Friends,
Postgres 7.3.4 on RH Linux 7.2.
I wanted to write a dynamic query for insert
statement.
create table test(c1 int, c2 varchar)
insert into test(c1, c2) values (1,'Hai1');insert into
test(c1, c2) values (NULL,'Hai2');
so I wrote a function called test_fn()
DECLARE
sqlstr VARCHAR(100);
rec RECORD;BEGIN FOR rec
IN SELECT * FROM test
LOOP sqlstr :=
'insert into test(c1, c2) values
(' ||rec.c1||','
||'\''||rec.c2||'\')';RAISE NOTICE
'%',sqlstr; execute
sqlstr; END LOOP;RETURN 'DONE';END;
NOTICE: insert into test(c1, c2) values
(1,'Hai1')NOTICE:
So i have created a null function.
sqlstr := 'insert into test(c1, c2) values
(' ||ISNULL(rec.c1,'')||','
||'\''||rec.c2||'\')';
Now I got results as
NOTICE: insert into test(c1, c2) values
(1,'Hai1')NOTICE: insert into test(c1, c2) values
(,'Hai2')WARNING: Error occurred while executing PL/pgSQL function
test_fnWARNING: line 11 at execute statement
ERROR: parser: parse error at or near "," at character
34
The error is because of no value for column c1. If the column
c1 is a string I might have replace it with empty string. I don't want to
substitute with '0' which could work.
sqlstr := 'insert into test(c1, c2) values
(' ||ISNULL(rec.c1,'0')||','
||'\''||rec.c2||'\')';
NOTICE: insert into test(c1, c2) values
(1,'Hai1')NOTICE: insert into test(c1, c2) values
(0,'Hai2')
Total query runtime: 47 ms.Data retrieval runtime: 0
ms.1 rows retrieved.
How can I do that. Please advise me.
Thanks
Kumar
Re: [SQL] How to avoid nulls while writing string for dynamic query
I am having problem there. see what happens
sqlstr := 'insert into test(c1, c2) values ('||COALESCE(rec.c1,'NULL')||','
||'\''||rec.c2||'\')';
WARNING: Error occurred while executing PL/pgSQL function test_fn
WARNING: line 8 at assignment
ERROR: pg_atoi: error in "NULL": can't parse "NULL"
Dont forgot that c1 is int.
when i have like this
sqlstr := 'insert into test(c1, c2) values ('||COALESCE(rec.c1,NULL)||','
||'\''||rec.c2||'\')';
NOTICE:
WARNING: Error occurred while executing PL/pgSQL function test_fn
WARNING: line 11 at execute statement
ERROR: cannot EXECUTE NULL query
That is the problem i am facing. Please shed some light.
Thanks
Kumar
- Original Message -
From: "Tomasz Myrta" <[EMAIL PROTECTED]>
To: "Kumar" <[EMAIL PROTECTED]>
Cc: "psql" <[EMAIL PROTECTED]>
Sent: Thursday, February 12, 2004 6:13 PM
Subject: Re: [SQL] How to avoid nulls while writing string for dynamic query
> Dnia 2004-02-12 13:31, Użytkownik Kumar napisał:
> > The error is because of no value for column c1. If the column c1 is a
> > string I might have replace it with empty string. I don't want to
> > substitute with '0' which could work.
> >
> > sqlstr := 'insert into test(c1, c2) values (' ||ISNULL(rec.c1,'0')||','
> >
> > ||'\''||rec.c2||'\')';
>
> Substitute it with NULL value:
> sqlstr := 'insert into test(c1, c2) values(' || coalesce(rec.c1,'NULL')
> ||...
>
> Regards,
> Tomasz Myrta
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] How to avoid nulls while writing string for dynamic query
Dear all,
I solved it using ISNULL function.
sqlstr := 'insert into test(c1, c2) values ('||ISNULL(rec.c1,'NULL')||','
> ||'\''||rec.c2||'\')';
Thanks
kumar
- Original Message -
From: "Kumar" <[EMAIL PROTECTED]>
To: "Tomasz Myrta" <[EMAIL PROTECTED]>
Cc: "psql" <[EMAIL PROTECTED]>
Sent: Friday, February 13, 2004 10:23 AM
Subject: Re: [SQL] How to avoid nulls while writing string for dynamic query
> I am having problem there. see what happens
>
> sqlstr := 'insert into test(c1, c2) values
('||COALESCE(rec.c1,'NULL')||','
> ||'\''||rec.c2||'\')';
>
> WARNING: Error occurred while executing PL/pgSQL function test_fn
> WARNING: line 8 at assignment
> ERROR: pg_atoi: error in "NULL": can't parse "NULL"
>
> Dont forgot that c1 is int.
>
> when i have like this
> sqlstr := 'insert into test(c1, c2) values ('||COALESCE(rec.c1,NULL)||','
> ||'\''||rec.c2||'\')';
>
> NOTICE:
> WARNING: Error occurred while executing PL/pgSQL function test_fn
> WARNING: line 11 at execute statement
> ERROR: cannot EXECUTE NULL query
>
> That is the problem i am facing. Please shed some light.
>
> Thanks
> Kumar
>
> - Original Message -
> From: "Tomasz Myrta" <[EMAIL PROTECTED]>
> To: "Kumar" <[EMAIL PROTECTED]>
> Cc: "psql" <[EMAIL PROTECTED]>
> Sent: Thursday, February 12, 2004 6:13 PM
> Subject: Re: [SQL] How to avoid nulls while writing string for dynamic
query
>
>
> > Dnia 2004-02-12 13:31, Użytkownik Kumar napisał:
> > > The error is because of no value for column c1. If the column c1 is a
> > > string I might have replace it with empty string. I don't want to
> > > substitute with '0' which could work.
> > >
> > > sqlstr := 'insert into test(c1, c2) values ('
||ISNULL(rec.c1,'0')||','
> > >
> > > ||'\''||rec.c2||'\')';
> >
> > Substitute it with NULL value:
> > sqlstr := 'insert into test(c1, c2) values(' || coalesce(rec.c1,'NULL')
> > ||...
> >
> > Regards,
> > Tomasz Myrta
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
---(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] How to avoid nulls while writing string for dynamic query
oh, ok understood.
What will happen for a timestamp field. Let us say c1 is a timestamp column.
sqlstr := 'insert into test(c1, c2) values
('||'\''||COALESCE(rec.c1,'NULL')||'\','
> > ||'\''||rec.c2||'\')';
If this case the query will be
insert into test(c1,c2) values ('2004-02-13', 'Hai')
If there is a null value encountered i will return an error for the
following query
insert into test(c1,c2) values ('NULL', 'Hai')
ERROR: Bad timestamp external representation 'NULL'
I think using 'CASE' this could be solved. But instead is there any other
simple way to do it.
Thanks a lot Mr. Tomasz Myrta
Kumar
- Original Message -
From: "Tomasz Myrta" <[EMAIL PROTECTED]>
To: "Kumar" <[EMAIL PROTECTED]>
Cc: "psql" <[EMAIL PROTECTED]>
Sent: Friday, February 13, 2004 12:03 PM
Subject: Re: [SQL] How to avoid nulls while writing string for dynamic query
> Dnia 2004-02-13 05:53, Użytkownik Kumar napisał:
>
> > I am having problem there. see what happens
> >
> > sqlstr := 'insert into test(c1, c2) values
('||COALESCE(rec.c1,'NULL')||','
> > ||'\''||rec.c2||'\')';
>
> You are preparing a string, so make sure you have strings everywhere:
> sqlstr := 'insert into test(c1, c2) values
> ('||COALESCE(rec.c1::text,'NULL')||','||'\''||rec.c2||'\')';
>
> Regards,
> Tomasz Myrta
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] How to avoid nulls while writing string for dynamic query
Thanks Tomasz Myrta. It is wonderful. I am still amazed from where you guys
knowing the options like quote_literal, etc.
Kumar
- Original Message -
From: "Tomasz Myrta" <[EMAIL PROTECTED]>
To: "Kumar" <[EMAIL PROTECTED]>
Cc: "psql" <[EMAIL PROTECTED]>
Sent: Friday, February 13, 2004 1:37 PM
Subject: Re: [SQL] How to avoid nulls while writing string for dynamic query
> Dnia 2004-02-13 08:13, Użytkownik Kumar napisał:
> > oh, ok understood.
> > What will happen for a timestamp field. Let us say c1 is a timestamp
column.
> >
> > sqlstr := 'insert into test(c1, c2) values
> > ('||'\''||COALESCE(rec.c1,'NULL')||'\','
> >
> >>>||'\''||rec.c2||'\')';
> >
> >
> > If this case the query will be
> > insert into test(c1,c2) values ('2004-02-13', 'Hai')
> >
> > If there is a null value encountered i will return an error for the
> > following query
> > insert into test(c1,c2) values ('NULL', 'Hai')
> > ERROR: Bad timestamp external representation 'NULL'
> It's because you can't use quotes with null. Valid query is:
> insert into test(c1,c2) values (NULL, 'Hai');
>
> Your dynamic query will then look like:
>
> sqlstr := 'insert into test(c1, c2) values (' ||COALESCE('\'' || rec.c1
> || '\'','NULL') ...
>
> or more elegant:
>
> sqlstr := 'insert into test(c1, c2) values ('
> ||COALESCE(quote_literal(rec.c1),'NULL') ...
>
> Regards,
> Tomasz Myrta
---(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] Getting the week of a date
Dear Friends,
Postgres 7.3.4 on RH Linux7.2.
While this works for month and why not for
week
test=# select date_trunc('month',current_date + ('5
month')::INTERVAL);
date_trunc- 2004-07-01 00:00:00(1
row)
test=# select date_trunc('week',current_date + ('5
month')::INTERVAL);ERROR: TIMESTAMP units 'week' not
supportedtest=#
Any idea on how to find the 3 rd Wednesday of any given month.
Thanks
Kumar
Re: [SQL] Getting the week of a date
Seems a part of your function always returns '0'
select 1 - (to_char(date_trunc('month',now()),'D'))::INT2
because while we use date_trunc it will always return the first day of the
month and when it get subtracted by '1' it be always zero. Is there any
reason why you have included that?
Thanks
Kumar
- Original Message -
From: "sad" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, February 16, 2004 6:53 PM
Subject: Re: [SQL] Getting the week of a date
EXCUSE ME, GUYS !
i forgot to add one monome:
7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4)
which is stands for skip a first week of month in case it is not consist Wed
finally the select will be similar the following
SELECT 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4) + 1 -
(to_char(date_trunc('month',now()),'D'))::INT2 + 7*3-3 ;
4 - is a number of Wed in a week (in postgresql numeration)
---(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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Getting the week of a date
I am sorry. I didn't read the doc properly. I understood why it is written
in that way.
Sorry for the trouble. Thanks. It worked fine for me.
Thanks
Kumar
- Original Message -
From: "Kumar" <[EMAIL PROTECTED]>
To: "sad" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, February 17, 2004 10:40 AM
Subject: Re: [SQL] Getting the week of a date
> Seems a part of your function always returns '0'
> select 1 - (to_char(date_trunc('month',now()),'D'))::INT2
>
> because while we use date_trunc it will always return the first day of the
> month and when it get subtracted by '1' it be always zero. Is there any
> reason why you have included that?
>
> Thanks
> Kumar
>
> - Original Message -
> From: "sad" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Monday, February 16, 2004 6:53 PM
> Subject: Re: [SQL] Getting the week of a date
>
>
> EXCUSE ME, GUYS !
>
> i forgot to add one monome:
> 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4)
> which is stands for skip a first week of month in case it is not consist
Wed
>
> finally the select will be similar the following
>
> SELECT 7*(((to_char(date_trunc('month',now()),'D'))::INT2-1)/4) + 1 -
> (to_char(date_trunc('month',now()),'D'))::INT2 + 7*3-3 ;
>
> 4 - is a number of Wed in a week (in postgresql numeration)
>
>
>
> ---(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
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Disabling constraints
Dear friends, I am working opn Postgres 7.3.4 on RH Linux 7.2. I wanted to disable constraints. Alter table 'table name' disable constraint 'constraint name'; doesn't work. I got some information from google, which says about indirect way of disabling and enabling a constraint, as follows. update pg_class set reltriggers=0 where relname = 'crm.activities';update pg_class set reltriggers = count(*) from pg_trigger where pg_class.oid=tgrelid and relname='crm.activities'; Also doesnt work. Is there a way to do it? Thanks Kumar
[SQL] Return more than a record
Dear Friends, Postgres 7.3.4 on RH Linux 7.2. Get the following from the groupscreate or replace function ExpensiveDepartments() returns setof table1 as ' declare r table1%rowtype; begin for r in select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid loop if (r.totalsalary > 7) then r.totalsalary := CAST(r.totalsalary * 1.75 as int8); else r.totalsalary := CAST(r.totalsalary * 1.5 as int8); end if; if (r.totalsalary > 10) then return next r.departmentid; end if; end loop; return; end ' language 'plpgsql'; Is possible for me to return a variable along with that 'return' statement? Because the table 'table1' contains some date column. I have done some calculation on those columns and want to return the calculated date along with that row of the table1. How to do that. Please shed some light.
Re: [SQL] Return more than a record
Dear Friends, I am using the record type as follows in my code. CREATE OR REPLACE FUNCTION fn_daily_calendar(date) RETURNS SETOF activities AS DECLARE p_cal_date ALIAS FOR $1; rec_activity activities%ROWTYPE; v_activity_start_date DATE; BEGIN FOR rec_activity IN SELECT * FROM activities WHERE DATE(activity_start_time) <= p_cal_date LOOP v_activity_start_date := rec_activity.activity_start_time::DATE; IF rec_activity.daily_gap IS NOT NULL AND rec_activity.recurrence_end_time IS NULL THEN LOOP v_activity_start_date := v_activity_start_date + rec_activity.daily_gap; IF v_activity_start_date = p_cal_date THEN RETURN next rec_activity; END IF; EXIT WHEN v_activity_start_date > p_cal_date + (1 month')::INTERVAL; END LOOP; END IF; END LOOP; RETURN; END; See I am fetching the activity_start_time from the record, then assigning to variable and do some calculations on the variable. Now I want to return the value of v_activity_start_date for every row in activities table. How could I achieve this. Please shed some light. Thanks Kumar - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "Kumar" <[EMAIL PROTECTED]> Cc: "psql" <[EMAIL PROTECTED]> Sent: Thursday, February 26, 2004 8:59 PM Subject: Re: [SQL] Return more than a record > On Thu, 26 Feb 2004, Kumar wrote: > > > Get the following from the groups > > create or replace function ExpensiveDepartments() returns setof table1 as > > Note that at least the example with this name in the SetReturningFunctions > guide seems to use setof int as the return type. > > > ' > > declare > > r table1%rowtype; > > begin > > for r in select departmentid, sum(salary) as totalsalary > > from GetEmployees() group by departmentid loop > > > > if (r.totalsalary > 7) then > > r.totalsalary := CAST(r.totalsalary * 1.75 as int8); > > else > > r.totalsalary := CAST(r.totalsalary * 1.5 as int8); > > end if; > > > > if (r.totalsalary > 10) then > > return next r.departmentid; > > end if; > > > > end loop; > > return; > > end > > ' > > language 'plpgsql'; > > Is possible for me to return a variable along with that 'return' statement? Because the table 'table1' contains some date > > column. I have done some calculation on those columns and want to return the calculated date along with that row of the > > table1. How to do that. Please shed some light. > > If you want to return a composite type, you can make another rowtype > variable that has the set of columns (and their types) to return, fill in > the values to return and then do return next with that variable. > > For example, to say return departmentid, sum(salary) and the computed > "totalsalary" from the above, you might do something like (untested so > there might be syntactic errors) > > create type holder as (departmentid int, totalsalary int8); > create type holder2 as (departmentid int, sumsalary int8, totalsalary > int8); > > create or replace function ExpensiveDepartments() returns setof holder2 as > ' > declare > r holder%rowtype; > s holder2%rowtype; > begin > for r in select departmentid, sum(salary) as totalsalary > from GetEmployees() group by departmentid loop > > s.departmentid := r.departmentid; > s.sumsalary := r.totalsalary; > > if (r.totalsalary > 7) then > s.totalsalary := CAST(r.totalsalary * 1.75 as int8); > else > s.totalsalary := CAST(r.totalsalary * 1.5 as int8); > end if; > > if (s.totalsalary > 10) then > return next s; > end if; > > end loop; > return; > end > ' > language 'plpgsql'; > > > The important differences here are that we've got a new rowtype variable s > of the return type and that we fill s with the values from r (the select) > plus the calculation that we're doing (rather than before where we just > overwrote the values in r.totalsalary) and then we return next s rather > than a particular field. > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] psql: FATAL 1: IDENT authentication failed for user error - Urgent pls
Dear Friends, I have installed Linux Fedore and wanted to work with the default installed postgres 7.3.4 database. I could able to create to create user, but while try to connect, I got the following error message psql: FATAL 1: IDENT authentication failed for user My pg_hba.conf file looks like below # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD local all all trusthost all all 192.168.2.0 255.255.255.0 trust I couldnt understand why. Please shed some light
[SQL] Rename Schema or Script DDL only a schema
Dear Friends, Postgres 7.3.2 on Linux 8. I wanna move about 100 tables from one schema to another (within a database). Is possible to do that? Seems pg_dump doesnt have an option to script only the schema. Else, it possible to rename the schema. Please shed some light. Thanks Kumar
[SQL] Encoding and result string length
Dear Friends,
Postgres 7.3.2 on Linux 8
I would like to fetch the datas from a table in a
encoded format.
create table encodeco(c1 int4, c2 int4);insert
into encodeco values(1, 2);select * from encodeco;
So I want to encode the data while
selecting.
select encode(c1,'base64') from
encodeco;
ERROR: Function encode(integer, "unknown") does not
exist Unable to identify a function that satisfies the given argument
types You may need to add explicit typecasts
So i tried
select encode('c1','base64')
from encodeco;
I got a result as
1
YzE=
But this is the encoded value for 'c1' and not for
the value 1.
Please shed some light.
Also is it possible to get the encoded values with
only 2 charactors, irrespective of the values of c1 ranging from 100 to 10
million
Thanks
Kumar
[SQL] Grant permission to all objects of user A to user B
Dear Friends, Postgres 7.3.2. I have an database with owner USRA who owns about 300 objects (tables and functions). Now I want to give 'ALL' permission to all the objects of USRA to another database user USRB. If I use the grant i need to type all the objects as comma separated, like the following booktown=# GRANT ALL ON customers, books, editions, publishers booktown-# TO manager; CHANGEIs there any other way do it. ThanksKumar
[SQL] Logical comparison on Strings
Dear Friends, Postgres 7.3.2 on Linux 7. I want to compare to columns and get the logical result as follows. C1 is 'YNYNY' . C2 is 'NNYYY'. I want to compare like AND and OR operators. C1 AND C2 should give result like NNYNY. C1 OR C2 should give result like YNYYY. Please shed some light. Thanks Kumar
[SQL] Customised Encoding
Dear Friends, Postgres 7.3.2 Runing on Linux 7.2 I would like to convert any values in between 1 to 2^16 into a single charactor. And decode that single char to get back the numeric value again. Any function to do that in Postgres SQL Server. Please shed some light. Thanks Kumar
[SQL] How to get binary value for an Integer
Dear Friends, I am using postgres 7.3.2. I wanna get the binary value of number 65536. Is there anyway to get that postgres functions. Thanks kumar
Re: [SQL] Customised Encoding
The idea is I wanna store any number from 1 to 2^16 in 2 strings only - This
is the requirement.
Since in Unicode a 16 bit is represented in a Single chararctor.
So i wanna convert any number into a 32 bit and then convert each 16 bit
into one char and stored it in database.
So I wanna know how to convert any 16 bit to a single charactor.
Is there any function to do that in Postgres.
Thanks
Kumar
- Original Message -
From: "Richard Huxton" <[EMAIL PROTECTED]>
To: "kumar" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, April 27, 2004 9:16 AM
Subject: Re: [SQL] Customised Encoding
> On Tuesday 27 April 2004 13:55, kumar wrote:
> > Hi Richar,
>
> Kumar - try to make sure you reply to the list.
>
> > It didnt work for me.
> >
> > select encode('65536'::bytea,'UTF-8')
> > ERROR: Cannot cast type integer to bytea
> >
> > select encode('65536'::bytea,'UTF-8')
> > ERROR: No such encoding as 'UTF-8'
>
> I think you're using the encode() function wrongly. The second parameter
is
> supposed to be something like "base64" or "hex". I'm not sure it makes
sense
> to try and cast an integer to a bytea either.
>
> Can you say what you're trying to achieve here?
>
> --
> Richard Huxton
> Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Equivalant of SQL Server's Nchar and NVARCHAR
Dear friends, Is there any equivalent datatype of SQL Server's NCHAR and NVARCHAR, available with Postgres 7.3.4. I want to store characters, special characters and Numbers. Please shed some light. Thanks Kumar
[SQL] Opening and Fetching from a refcursor inside a function
Dear Friends, I am using Postgres 7.3.4 on Redhat linux In one of my requirement, I am getting a cursor as input parameter.I am trying to process the records inside this cursor and i am not able to do it. (Some thing like opening and fetching a cursor in SQL Server) My approach is as follows. fn_xyz(refcursor,int4) returns recordDeclaredata ALIAS $1;mview RECORD;FOR mview IN FETCH ALL IN dataLOOP//Process the records.END LOOP; Is this possible. Please shed some light. Thanks Kumar
[SQL] Last day of month
Dear friends, Postgres 7.3.4 How to find the last sunday/mon/sat of any given month. Thanks Kumar
Re: [SQL] Order By Question
> This seems like the answer must be pretty easy, but I can't think of it: > > In the following statement: > > select field1 from my_table where field2 in (3, 1, 2); > > How can I modify this statement so that the record are returned in the > order of first those records having field2 = 3, then field2 = 1, then > field2 = 2. > As it stands, I am getting them returned in the order of the value of > field1. > One way is to have a priority table where each value is mapped to its associated priority and then you do a join against this table and order by the priority value instead. So you might have a table like follows: priorityvalue 1 3 2 1 3 2 -- Prasanth Kumar [EMAIL PROTECTED]
[SQL] Privileges on Functions in Postgres 7.1.3
Hi, Is it possible to allow a user to execute a procedure that will update some tables without the user having any privileges on the underlying tables themselves (like in Oracle where the procedure effectively runs with the privilege of the creator)? Regards, _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(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] Problem with temporary table -- Urgent
Hi,
We are using postgresql 7.3.3, we are encountering
the following problems when we used temporary tables.
Here with i'm sending my Sample function.
create or replace function
TestTemp_refcur(refcursor) returns refcursor As 'declare refc alias
for $1; lString
Varchar(4000);begin lString :=
'' CREATE OR REPLACE FUNCTION TESTTEMP() RETURNS varchar as
BEGIN
''; lString := lString
|| '' create temporary table temp_Table( Name Numeric);'';
lString := lString || ''
insert into temp_Table values (1); '';
lString := lString || ''
insert into temp_Table values (2);'';
lString := lString || ''
return null; end; language plpgsql;'';
raise notice '' Notice is %
'', lString; execute
lString; open refc for select * from temp_Table; return
refc;end;' language 'plpgsql';
begin;select
TestTemp_refcur('funcursor');fetch all in funcursor;commit;
psql:test18.sql:25:
WARNING: Error occurred while executing PL/pgSQL function
testtemp_refcurpsql:test18.sql:25: WARNING: line 20 at
openpsql:test18.sql:25: ERROR: Relation "temp_table" does not
existpsql:test18.sql:26: ERROR: current transaction is aborted,
queries ignored until end of transaction block
Kindly guide as to solve this
problem
Any help will be highly
appreciated
Thanks & Regards
Vijay
[SQL] problem with temporary table.
Hi,
We are using postgresql7.3.3, we
are encountering some problems by using temporary tables.
Actually our requirement was,
1. create
temporary table.
2. insert
some values on that table by using some quries.
3.
select the inserted values from the temporary table.
To fullfil the above requirement, we wrote the
below functions..kindly go through the below functions and
guide us to come out from this temporary table
problem.
1. Call the same function more than ones in
the same connection.
eg;
create or replace function TestTemp_refcur(refcursor) returns
refcursor as '
declare
refc alias for $1;
begin
create temporary table temp_table(idno
numeric,iname varchar(10));
insert into temp_table values
(1,''ganesh'');
insert into temp_table values
(2,''John'');
open refc for select * from
test_temp_table;
return refc;
end;
' language
'plpgsql';
begin; select
TestTemp_refcur('funcursor'); fetch all
in funcursor;
commit;
The above
function is working fine for the first call, from next call onwards it is
throwing the below error.
Error: relation 'temp_table' already exists.
2. To avoid this, we had gone through some
of postgresql faq and documents. There some one suggested to create
temporary table by Execute.
So that, we created one function,
inside that fucntion we created one more function to take care of creating
temporary table.
Eg,
create or replace
function TestTemp_refcur(refcursor) returns refcursor As '
declare refc
alias for $1;
lString Varchar(4000);
begin lString
:= '' CREATE OR REPLACE FUNCTION TESTTEMP() RETURNS varchar as
BEGIN
'';
lString := lString || '' create temporary table
temp_Table( Name Numeric);'';
lString := lString || '' insert into temp_Table values (1);
'';
lString := lString || '' insert into temp_Table values
(2);'';
lString := lString || '' return null; end; language
plpgsql;'';
raise notice '' Notice is % '',
lString;
execute
lString;
open refc for select * from
temp_Table;
return refc; end; ' language
'plpgsql';
begin; select
TestTemp_refcur('funcursor'); fetch all in
funcursor; commit;
With the above approach, we are getting the below
error.
Error :
Relation "temp_table" does not exist
Any kind of info/soln/help will be highly
appreciated..
Thanks & Regards
Vijay
[SQL] Unable to execute Java Progarm
Dear Sir, When I tried to execute sample Java program using Postgresql its showing runtime error Exception in thread "main" java.lang.NoClassDefFoundError: java/sql/Savepoint at org.postgresql.Driver.connect(Driver.java:183) at java.sql.DriverManager.getConnection(DriverManager.java:517) at java.sql.DriverManager.getConnection(DriverManager.java:177) at NotificationTest.main(NotificationTest.java:13)Pl give me the solution for this problem Waiting eagerly for ur reply Regards Anil Kumar.S BEGIN:VCARD VERSION:2.1 N:Kumar;Anil FN:Anil Kumar EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20041222T120758Z END:VCARD ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Question about domains.
On 08/07/10 2:27 PM, Dmitriy Igrishin wrote: Hey all, Is there a way to add constraint to the domain that used by a composite type that used by a table? E.g.: Currently in PG, adding constraint on Domain, which is already in use is not supported. CREATE DOMAIN superid AS integer; CREATE TYPE idtype AS ( id superid ); CREATE TABLE mytab (id idtype NOT NULL); ALTER DOMAIN superid ADD CONSTRAINT superid_check CHECK (VALUE > 0); ALTER DOMAIN superid DROP CONSTRAINT superid_check; produces the following output: dmitigr=> CREATE DOMAIN Time: 23,809 ms dmitigr=> CREATE TYPE Time: 44,875 ms dmitigr=> CREATE TABLE Time: 134,101 ms dmitigr=> ERROR: cannot alter type "superid" because column "mytab"."id" uses it dmitigr=> ALTER DOMAIN Time: 0,270 ms As you can see, adding constraint to the domain produces an error, while dropping constraint is possible! Any comments? If you want, you can try following: CREATE DOMAIN superid1 AS integer check(value > 0); create type idtype1 as (id superid1); create or replace function idtype2idtype1(idtype) returns idtype1 as $$ select row($1.id)::idtype1; $$ language sql; create cast (idtype as idtype1) with function idtype2int(idtype) as implicit; Then execute the alter table command to convert the data type: alter table mytab alter column id type idtype1; -- Thanks& Regards, Vibhor Kumar. EnterpriseDB Corporation The Enterprise Postgres Company -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pgdump with insert command help
On Sep 24, 2010, at 6:02 PM, Nicholas I wrote: > > pg_dump -Dt --insert table dbname > table.sql; > > i am not able to get the output. is this correct ? Try following: pg_dump --insert -t Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Issue while using PostgreSql 8.4.
Hi, I am using PostgreSql 8.4 for my web application also am using following technology stacks: 1> Java 2> Window XP (OS). 3> JBoss Server4.2. My issue is, first time I am creating the table and inserting some rows of data. After doing some logic going to delete that table . I am observing application is getting hang while executing statement.execute(). Please suggest me how to fix this issue. Thanks Atul Kumar DISCLAIMER == This e-mail may contain privileged and confidential information which is the property of Persistent Systems Ltd. It is intended only for the use of the individual or entity to which it is addressed. If you are not the intended recipient, you are not authorized to read, retain, copy, print, distribute or use this message. If you have received this communication in error, please notify the sender and delete all copies of this message. Persistent Systems Ltd. does not accept any liability for virus infected mails.
Re: [SQL] TABLE PARTITION
On Feb 1, 2011, at 10:31 PM, NEVIN ALEX wrote: > Hi, > I am Nevin Alex and I am using postgresql database for a year . But I > have’nt used table partitions: Please help me to do it in a better way. I got > it from the documentation that it is an inheritance capability and the > Trigger working but for dynamic data how can I implement that. > Please elaborate more about the data which you are talking about. > Thanks in advance -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Compare two Data bases Structure
On Feb 24, 2011, at 5:33 AM, manuel antonio ochoa wrote: > How Can I do to compare two structures of data bases ? > > DBA != DBB I need wich functions and wich tables are not equals > > thnks You can try with apgdiff. http://apgdiff.startnet.biz/ Thanks & Regards, Vibhor -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with serial counters
On Feb 25, 2011, at 2:39 AM, Jacques Lebrun wrote: > What can I do to force PostGres to update the internat serial counter when I > do an insert with a specified value for the serial? > After inserting the all the Data, use ALTER SEQUENCE Command: http://www.postgresql.org/docs/8.4/static/sql-altersequence.html Thanks & Regards, Vibhor -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] what's wrong in this procedure?
On Feb 25, 2011, at 11:16 PM, Camaleon wrote:
> This error is returned Erro de SQL:
>
> ERROR: column "Aguardando Pagto" does not exist at character 352 >>>
>
>
> create or replace function get_historico() RETURNS SETOF
> twiste.type_cur__historico AS '
>
> SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS
> transacoes
> FROM ofertas o
> JOIN transacao t ON o.ofertas_id = t.ofertas_id
> JOIN municipio m ON o.municipio_id = m.municipio_id
> WHERE o.data_fim <= now() AND t.status IN("Aguardando Pagto", "Em análise",
> "Aprovado", "Completo")
> GROUP BY o.data_fim;
> '
> language 'sql';
try Following:
create or replace function get_historico() RETURNS SETOF
twiste.type_cur__historico AS $$
SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS
transacoes
FROM ofertas o
JOIN transacao t ON o.ofertas_id = t.ofertas_id
JOIN municipio m ON o.municipio_id = m.municipio_id
WHERE o.data_fim <= now() AND t.status IN('Aguardando Pagto', 'Em análise',
'Aprovado', 'Completo')
GROUP BY o.data_fim;
$$
language 'sql';
Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
vibhor.ku...@enterprisedb.com
Blog:http://vibhork.blogspot.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] converting big int to date
On Mar 24, 2011, at 2:42 AM, Sree wrote: > How can i convert bigint to date format. > > bigint=6169625280 Please explain what;s this value shows. Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to realize ROW_NUMBER() in 8.3?
On Apr 20, 2011, at 9:15 PM, Emi Lu wrote: > ROW_NUMBER() is only ready in 8.4. For 8.3, is there a simple way to get > row_number > select row_number(), col1, col2... > FROM tableName Following is a link of deepsz which has a way of implementation of rownum. http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/ Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] new user on mac
On Oct 19, 2011, at 7:38 AM, Adam Cornett wrote: > > On Tue, Oct 18, 2011 at 5:47 PM, Scott Swank wrote: > I have a postgres 9.1 database up & running, no problem. Purely in > terms of writing sql (ddl, dml & pg/plsql), what tools are > recommended? > > Coming from an Oracle world, I'm thinking of toad, sql developer, etc. > > 1. psql & text editor of choice (if so, which one?) > 2. navicat > 3. textmate with pgedit > 4. eclipse plugin > 5. other? > > Thank you, > Scott > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > > psql + editor is the basic tool set, certainly the most flexible. > Also be sure to check out pgadmin: http://www.pgadmin.org/download/macosx.php +1 for pgAdmin3. If you have already used Toad, u would like to check it. Thanks & Regards, Vibhor Kumar Blogs:http://vibhork.blogspot.com http://vibhorkumar.wordpress.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Change of data type
Use Alter table notebook from control center right click on the table and open alter table notebook you can drop a column or add a column or change the datatype Kumar On 8/7/06, Judith <[EMAIL PROTECTED]> wrote: Hello everybody, excuse me how can I change de data type of a field,I currently have: material character(30) but I now want the field in text type like this: material text somebody knows if ALTER TABLE has some option to do this?, or Howcan I do that?---(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 -- KumarDB2 DBA & SAP Basis professional
[SQL] sql query question ?
Hi All, I have a table called vehicle_duty_cycle_summary vehicle_master_id | starting_odometer | ending_odometer | login_time | logout_time ---+---+-++ 4 | 53379.00 |53504.00 | 2006-12-19 16:19:16.584547 | 2006-12-20 07:12:57.716907 4 | 51310.00 | 51457.00 | 2006-12-05 16:04:51.585441 | 2006-12-06 07:18:10.251535 4 | 42411.00 | 42411.00 | 2006-09-14 16:03:49.541442 | 2006-09-15 08:24:41.308339 4 | 54012.00 | 54146.00 | 2006-12-25 16:14:31.313728 | 2006-12-26 07:05:55.82487 4 | 43071.00 | 43181.00 | 2006-09-21 11:24:43.466766 | 2006-09-22 07:10:09.362792 4 | 51208.00 | 51305.00 | 2006-12-04 16:14:14.600105 | 2006-12-05 08:03:38.139832 4 | 50235.00 | 50370.00 | 2006-11-27 15:55:48.866925 | 2006-11-28 07:27:25.898991 4 | 53231.00 | 53362.00 | 2006-12-18 16:06:42.764133 | 2006-12-19 07:06:03.450072 4 | 52656.00 | 52818.00 | 2006-12-13 16:03:47.189303 | 2006-12-14 07:59:11.463733 4 | 50801.00 | 50880.00 | 2006-12-02 16:03:55.666321 | 2006-12-03 06:53:21.433746 9 | 85360.00 | 85493.00 | 2007-06-10 07:17:12.330974 | 2007-06-10 22:11:04.422656 9 | 78009.00 | 78042.00 | 2007-03-12 17:53:18.794001 | 2007-03-12 20:42:39.439647 9 | 84529.00 | 84679.00 | 2007-06-01 06:42:09.306306 | 2007-06-01 20:35:54.317172 9 | 78058.00 | 78149.00 | 2007-03-13 10:08:48.696709 | 2007-03-13 21:50:31.136412 9 | 86506.00 | 86595.00 | 2007-06-21 09:28:40.504082 | 2007-06-21 23:15:41.862292 9 | 78155.00 | 78239.00 | 2007-03-14 09:32:58.512817 | 2007-03-14 20:58:24.36362 9 | 84894.00 | 85012.00 | 2007-06-04 07:59:00.896969 | 2007-06-04 18:42:13.791974 9 | 78435.00 | 78494.00 | 2007-03-16 07:48:23.626402 | 2007-03-16 21:39:09.479043 9 | 83992.00 | 84045.00 | 2007-05-25 07:25:20.462928 | 2007-05-25 21:23:43.697577 9 | 78506.00 | 78595.00 | 2007-03-17 08:01:06.003564 | 2007-03-17 19:48:32.383689 9 | 85493.00 | 85640.00 | 2007-06-11 06:58:03.052538 | 2007-06-11 22:56:13.134053 9 | 78279.00 | 78395.00 | 2007-03-15 08:00:58.198265 | 2007-03-15 21:39:00.052173 I would like to compute the following on this table. Idle time of vehicel=(ending_odometer reading of the previous day - starting_odometer reading of the present day) for every vehicle can anybody help me this issue. Thanks in advance. Trilok __ Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] sql query question ?
Dear Shane, Thanks for the reply and your observation about the word i have used. It is idle odometer reading. The actual Scenario is that the vehicle is taken by the driver. When he comes the next day. He is suppose to login again. Here i am trying to find out how much distance has the vehicle travelled before it login again,give a date range and the vehicle id. The below query would give me the total odometer reading during a single trip. select vehicle_master_id, (ending_odometer - starting_odometer) as unmetered_travel from vehicle_duty_cycle_summary; Thanks Trilok --- Shane Ambler <[EMAIL PROTECTED]> wrote: > Trilok Kumar wrote: > > Hi All, > > > > I have a table called > > > > vehicle_duty_cycle_summary > > > > > > vehicle_master_id | starting_odometer | > > ending_odometer | login_time | > > > logout_time > > > ---+---+-++ > > > > 4 | 53379.00 |53504.00 | > 2006-12-19 > > 16:19:16.584547 | 2006-12-20 07:12:57.716907 > > > > > I would like to compute the following on this > table. > > > > Idle time of vehicel=(ending_odometer reading of > the > > previous day - > > starting_odometer reading of the present day) for > > every vehicle > > I would think your naming may be confusing and may > not be > implemented(recorded?) very well. > > I think Idle Time is a misleading name by your > explanation - Idle time > would be defined as (logout_time - previous > login_time) which gives you > the time the vehicle was sitting in the garage. > > What you want may be better called unmetered_travel > and would be the > distance traveled between login_time and logout_time > This would simply be > select vehicle_master_id, > (ending_odometer - starting_odometer) as > unmetered_travel > from vehicle_duty_cycle_summary; > > Going by the naming you have used it would appear > that you are recording > the time spent in the garage (going by the data you > have shown I would > say this is a company car garage not a repair shop) > > One record would appear to record the time the car > is in the garage - > login_time would be the time the employee returned > the car and > logout_time would be when the car next went out to > someone. > I would think you want the opposite of that - the > time and odometer > reading when an employee takes the car and the time > and odometer of when > it is returned and the employee_id of who had it. > This will give you who > used the car at what time and what distances they > travelled (which of > course would be work related travel) > > Going with those changes - > > The distance traveled by an employee is easy to > workout, if you wanted > to workout the unmetered (non-work) distance > traveled you could try > something like (untested) - > > select > v1.vehicle_master_id > , v1.starting_odometer - > (select v2.ending_odometer >from vehicle_duty_cycle_summary v2 > >where v2.vehicle_master_id = v1.vehicle_master_id >and v2.login_time < v1.logout_time > >order by v2.login_time desc limit 1) > as unmetered_travel > > from vehicle_duty_cycle_summary v1 > > where v1.vehicle_master_id = 4; > > > I would calculate idle time as - > > select > v1.vehicle_master_id > , v1.logout_time - > (select v2.login_time >from vehicle_duty_cycle_summary v2 > >where v2.vehicle_master_id = v1.vehicle_master_id >and v2.login_time < v1.logout_time > >order by v2.login_time desc limit 1) > as unmetered_travel > > from vehicle_duty_cycle_summary v1 > > where v1.vehicle_master_id = 4; > > > If this isn't the way it should work you should be > able to adapt the > query to match your definition of idle time. > > > -- > > Shane Ambler > pgSQL (at) Sheeky (dot) Biz > > Get Sheeky @ http://Sheeky.Biz > ___ Yahoo! Answers - Got a question? Someone out there knows the answer. Try it now. http://uk.answers.yahoo.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] AUTO INCREMENT
i am searching for autoincrement in table in table employee i want give empno as autoincrement is there any syntax like this create table emp (empno integer auto_increment); send reply ganesh __ Do You Yahoo!? Yahoo! Photos -- now, 100 FREE prints! http://photos.yahoo.com
[SQL] BACK UP
I WANT TO TAKE BACK UP DAILY WORK DONE WHAT IS PROCEDURE IS THERE ANY COMMMAND GANESH __ Do You Yahoo!? Yahoo! Photos -- now, 100 FREE prints! http://photos.yahoo.com
[SQL] sql foregein key
sir , i am working 6.5.2 postgresql in creation foregein key in table syntax i am writing is 1)create table gk (sno int primary key); 2)create table kk (sno int references gk, sname varchar(2)); i am getting message like this foregein key is not implemented . please write which version it supports . please send reply assp ganesh __ Do You Yahoo!? Yahoo! Photos -- now, 100 FREE prints! http://photos.yahoo.com
Re: [SQL] sql programming
"Michael Wagner" <[EMAIL PROTECTED]> writes: > We need to export an SQL database to Excel. Is this within your scope and = > what might your cost be? > > Please respond to Dan Beach Why not just save it to text CSV format and Excel can then easily import it. -- Prasanth Kumar [EMAIL PROTECTED]
Re: [SQL] question on SELECT
Howard Hiew <[EMAIL PROTECTED]> writes: > Hi, > I would like to know what is the sql statement that list all the tables > name. > > For example in Oracle, > 'SELECT TABLE_NAME from ALL_TABLES where owner="Oracle" '; > > What is the statement for Postgres? > Thank you > > Best Regards, > Howard > CIM/MASTEC > Tel:(65)8605283 You can do '\dt' to list all tables. There is also a system table 'pg_tables' which you can use if you like to do a select instead. Do SELECT tablename FROM pg_tables where tableowner='postgres'; -- Prasanth Kumar [EMAIL PROTECTED]
Re: [SQL] sequence chages after firing update
dear subha, Use explicit ORDER BY if u want to order the records by some column. otherwise the order of output from a select stmt is undefined. bu generally it is found the the last updated record comes last. On Wednesday 26 June 2002 17:17, Subhashini Karthikeyan wrote: > hi all > > > In postgresql 7.1.3 > > i am updateing a row. it is a 4th record. > after updation if i am firing a select query it is > coming as a last record ..what shall i do to avoid > that.. > any help appriciated > > thankz in advance > > regards > subha > > > __ > Do You Yahoo!? > Yahoo! - Official partner of 2002 FIFA World Cup > http://fifaworldcup.yahoo.com > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] Efficient Query For Mapping IP Addresses To Country Code.
Hi folks, the problem is to update one table by querying another. i have a table where i store apache access logs where one of the fields is the host ip address. i need to find corresponding country for all the ip addrresses. for this i have another table that contains apnic,arin and ripe databases in the form of: Table "ip_country_map" Column | Type | Modifiers --+--+--- start_ip | inet | end_ip | inet | country | character(2) | Indexes: end_ip_idx, start_ip_idx I need to update the accees log's country field by searching the ip in ip_country_map for country. i have follwing three alternatives , all seems to be slow. 1 st. (based on implicit join) - explain UPDATE access_log_2002_06_25 set country=ip_country_map.country where host_ip between ip_country_map.start_ip and ip_country_map.end_ip; Nested Loop (cost=0.00..1711037.55 rows=5428333 width=563) -> Seq Scan on ip_country_map (cost=0.00..1112.55 rows=48855 width=70) -> Seq Scan on access_log_2002_06_25 (cost=0.00..20.00 rows=1000 width=493) --- 2nd (based on subselect) --- explain UPDATE access_log_2002_06_25 set country=(select country from ip_country_map where access_log_2002_06_25.host_ip between start_ip and end_ip); NOTICE: QUERY PLAN: Seq Scan on access_log_2002_06_25 (cost=0.00..20.00 rows=1000 width=493) SubPlan -> Seq Scan on ip_country_map (cost=0.00..1356.83 rows=5428 width=6) EXPLAIN 3 rd (do not update country field at all just join both the table) explain SELECT host_ip,ip_country_map.country from access_log_2002_06_25 join ip_country_map on ( host_ip between start_ip and end_ip) ; NOTICE: QUERY PLAN: Nested Loop (cost=0.00..1711037.55 rows=5428333 width=102) -> Seq Scan on ip_country_map (cost=0.00..1112.55 rows=48855 width=70) -> Seq Scan on access_log_2002_06_25 (cost=0.00..20.00 rows=1000 width=32) EXPLAIN Yet Another option -- while loading access_log from file into database i do a select on ip_country_map. also even a simple query like do not use indexes. access_log=# explain SELECT country from ip_country_map where start_ip <= '203.196.129.1' and end_ip >= '203.196.129.1'; NOTICE: QUERY PLAN: Seq Scan on ip_country_map (cost=0.00..1356.83 rows=5428 width=6) EXPLAIN access_log=# explain SELECT country from ip_country_map where '203.196.129.1' between start_ip and end_ip; NOTICE: QUERY PLAN: Seq Scan on ip_country_map (cost=0.00..1356.83 rows=5428 width=6) EXPLAIN access_log=# IS THERE ANYTHING woring with my database schema? how shud i be storing the the data of ipranges and country for efficient utilization in this problem. regds Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] how to write procedures
Hi , Stored procedures are supported in pgsql for quite a long time consult postgresql docs on website http://www.postgresql.org/idocs/index.php?xplang.html or your local installations. regds malz. On Thursday 04 July 2002 16:15, srikanth wrote: > Hi, I am using postgre sql server on linux server but for my database I am > using storedprocedures which i need to create , but there are no commands > to create procedures it says it does not support is there any way to work > with stored procedures in postgre sql server. > thanks, > srikanth. > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Can this be done with sql?
Hi , you can use GROUP BY , at the expense of adding one more column of SERIAL data type, say, select * from t_a limit 10; access_log=# SELECT * from t_a limit 15; sno | value -+--- 1 | 4533 2 | 2740 3 | 9970 4 | 6445 5 | 2220 6 | 2301 7 | 6847 8 | 5739 9 | 5286 10 | 5556 11 | 9309 12 | 9552 13 | 8589 14 | 5935 15 | 2382 (15 rows) if you want avg for every third item you can use: access_log=# SELECT avg(value) from t_a group by (1+(sno-1)/3) limit 5; avg - 5747.67 3655.33 5957.33 8139.00 5635.33 (5 rows) you can replace 3 in the SQL with any number for grouping that many records. if you need MEAN , STDDEV , MAX, MIN etc you can use approprite AGGREGATE that PGSQL supports for numbers eg for MAX access_log=# SELECT MAX(value) from t_a group by (1+(sno-1)/3) limit 5; max -- 9970 6445 6847 9552 8589 (5 rows) Regds MAlz. On Thursday 04 July 2002 00:02, teknokrat wrote: > In my database i have values recorded in one minute intervals. I would > like a query that can get me results for other time intervals. For > example - return maximum value in each 3 minute interval. Any ideas > how i can do this with sql? I tried writing a procedure in plsql but i > am told it does not support tuples as output. I can get the all the > one minute intervals and process them to get me three minute intervals > in my application but i would rather not do the expensive call for the > one minute intervals in the first place due to the large number of > data. any ideas? > > thanks > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
