[SQL] Function with default value?
Hi, Is it possible to define a function with some default values? CREATE OR REPLACE FUNCTION foo(i IN int, j IN int DEFAULT := 1) … Anyway, I didn’t find such a feature described in the PostgreSQL 8.1 documentation, but sometimes, that doesn’t mean that the feature doesn’t exist! J Thanks, Daniel
Re: [SQL] Function with default value?
Daniel CAUNE <[EMAIL PROTECTED]> writes: > Is it possible to define a function with some default values? > CREATE OR REPLACE FUNCTION foo(i IN int, j IN int DEFAULT := 1) No. But you can fake many versions of this with a family of functions: CREATE OR REPLACE FUNCTION foo(i IN int, j IN int) ... CREATE OR REPLACE FUNCTION foo(i IN int) ... return foo(i, 1) ... Remember that PG lets you "overload" a function name by using the same name with different parameter lists. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Function with default value?
> -Message d'origine- > De : Tom Lane [mailto:[EMAIL PROTECTED] > Envoyé : dimanche 29 janvier 2006 10:48 > À : Daniel CAUNE > Cc : pgsql-sql@postgresql.org > Objet : Re: [SQL] Function with default value? > > Daniel CAUNE <[EMAIL PROTECTED]> writes: > > Is it possible to define a function with some default values? > > CREATE OR REPLACE FUNCTION foo(i IN int, j IN int DEFAULT := 1) > > No. But you can fake many versions of this with a family of functions: > > CREATE OR REPLACE FUNCTION foo(i IN int, j IN int) ... > > CREATE OR REPLACE FUNCTION foo(i IN int) ... return foo(i, 1) ... > > Remember that PG lets you "overload" a function name by using the same > name with different parameter lists. > > regards, tom lane Yes, thanks Tom, for the second time. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] variable scooping
hi all, I'm a newbie in postgresql. I've tried to make function but having trouble in variable scooping. here is the code in plpgsql: "declare tbl_name varchar:='tbl_A'; begin if exists(select 1 from pg_tables where tablename=tbl_name) then select count(*) from tbl_name; end if; end" the message was: syntax error in $1 in "select count(*) from $1". I thought variable 'tbl_name' was not recognized. Why ? Please need some help here. Thx very much. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to implement Microsoft Access boolean (YESNO)
On Tue, 24 Jan 2006 07:53, Greg Stark wrote: > > Having checked the I/O format it seems that MS Access exports the > > values of a YESNO field as 0 and 1 Hmmm. I may be wrong, but last time I looked (a year or so ago), when I cast MS-Access yes/no fields to numerics, it gave me 0=no or all_bits_on=yes (which because MS lacks "unsigned", is -1). -- David T. Bath System Analyst, Challenge Logistics 75-85 Nantilla Road, Clayton North Vic 3168 Voice: 131323 Fax: +613 8562 0002 [EMAIL PROTECTED] IMPORTANT - This email and any attachments is confidential. If received in error, please contact the sender and delete all copies of this email. Please note that any use, dissemination, further distribution or reproduction of this message in any form is strictly prohibited. Before opening or using attachments, check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files, our liability is limited to resupplying any affected attachments. Any representations or opinions expressed in this email are those of the individual sender, and not necessarily those of the Capital Transport Services. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [GENERAL] hi all......................!!
AKHILESH GUPTA schrieb: > hello everybody > i am new to this mailing list. this is my first mail to this group. > i jussst want to confirm that whether is it possible to update a view or > not?? > i think you all help me in solving my queries in future...!! Yes it is. All you have to do is to add a rule for updating in the way you want it to work. When you use pgadmin3 or such tools, you see that a view is basically a table with one rule for select. For Insert, update you can add a rule any time. HTH Tino Wildenhain ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Question about check constraints
Hi all, I have a table where two columns have two different check constraints associated with them. When I update one column, the check constraint on the other column is also executed. Is there a way to avoid this? I want to check only for the condition defined for the column being updated. Thanks, Kashmira
[SQL] UPDATE with correlated aggregates
Hi, I have a database with one "main" table with three columns that self-reference the primary key, plus four other tables that have (in total) seven foreign key columns referencing "main". I want to create a table (or view) that summarizes the various reference counts, per row in "main". I have an approach that works, for the much simpler situation described below, but wanted feedback on possible improvements or simplifications before implementing it on the actual tables. Here are the simplified "main" table and one of the ancillary tables: dev=> \d main Table "public.main" Column | Type | Modifiers +-+--- id | integer | not null parent | integer | data | text| Indexes: "main_pkey" PRIMARY KEY, btree (id) dev=> \d items Table "public.items" Column | Type | Modifiers +-+--- id | integer | not null main | integer | data | text| Indexes: "items_pkey" PRIMARY KEY, btree (id) dev=> select * from main; id | parent | data ++ 1 || ABC 2 | 1 | ABCDEF 3 | 1 | ABCGHI 4 || PQR 5 | 4 | PQRSTU 6 | 4 | PQRUVW 7 | 4 | PQRXYZ (7 rows) dev=> select * from items; id | main | data +--+-- 1 |2 | asdf 2 |2 | jkl; 3 |2 | qwer 4 |3 | uiop 5 |3 | m,./ 6 |4 | zxcv 7 |4 | rtyu 8 |4 | fghj 9 |4 | vbnm 10 |6 | asl; 11 |7 | qwop 12 |7 | zx./ (12 rows) This is the summary table: dev=> \d summ Table "public.summ" Column | Type | Modifiers +-+--- id | integer | not null subs | bigint | items | bigint | Indexes: "summ_pkey" PRIMARY KEY, btree (id) I first populate "summ" with 'select id from main'. Since an UPDATE apparently cannot include aggregates, i.e., subs = count(*), I chose to create two views as follows: CREATE VIEW main_summ AS SELECT parent AS id, count(*) AS subs FROM main WHERE parent IS NOT NULL GROUP BY parent; CREATE VIEW items_summ AS SELECT items.main AS id, count(*) AS items FROM items GROUP BY items.main; Then I update "summ" as follows: update summ set subs = ms.subs from main_summ ms where ms.id = summ.id; update summ set items = its.items from items_summ its where its.id = summ.id; The end result is: dev=> select * from summ order by id; id | subs | items +--+--- 1 |2 | 2 | | 3 3 | | 2 4 |3 | 4 5 | | 6 | | 1 7 | | 2 (7 rows) In the real database, I'd probably have to create 10 views and do 10 updates so any simpler approach would be appreciated. I played with creating an intermediate table that was populated from UNION SELECTs of the various GROUP BYs, each with an additional column that coded the type of relationship, but couldn't figure out how to create the summary since the coded column has to be used to add the count to either the "subs" or "items" columns (or subcolumns by type). Thanks for any suggestions and comments. Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] [GENERAL] How to find a temporary table
http://archives.postgresql.org/pgsql-general/2006-01/msg01259.php On 1/27/06, Emil Rachovsky <[EMAIL PROTECTED]> wrote: > > Hi, > I am using PostgreSQL 8.1.0 . How can I find a temp > table from my session, having the name of the table? > Can anyone show me what query should I execute? I've > tried some things but I receive mixed results of > tables from different sessions, which is strange. > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] variable scooping
On Mon, Jan 30, 2006 at 09:01:37AM +0700, ody quraviharto wrote: > "declare tbl_name varchar:='tbl_A'; > begin > if exists(select 1 from pg_tables where tablename=tbl_name) then > select count(*) from tbl_name; > end if; > end" > > the message was: syntax error in $1 in "select count(*) from $1". You'll need to use EXECUTE for this query; see "Executing Dynamic Commands" in the PL/pgSQL documentation. Here's the link for 8.1 (but use the documentation for whatever version you're running): http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] regarding debugging?
hi all, i have a query regarding debbuging in PGSQL. just like there is a debugger in C/C++, where we can check the execution of our program or we can dry run our code, is there aby option or feature with PGSQL for the same purpose that we can check our PGSQL statements? thanks in advance!! (waiting for your response)-- Thanks & Regards,AkhileshDAV Institute of ManagementFaridabad(Haryana)GSM:-(+919891606064) (+911744293789)"FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"
