[SQL] view problem - too many rows out
Hi folks. I've got a database for my consumable stock. I have a consumables table containing the type details, e.g. HP 4100 toner. I then have a cons_stock table holding item details, one record per item (2 toners = 2 rows). I have a order_dets view which pulls in all the data required including order details (order number, state, supplier etc.), location description, supplier details etc. This works fine. I now want a variation of this, but instead of showing individual items I want to show only consumable types and a quantity. Below are the two views, along with example output. Can anyone see where I've gone wrong? create view order_dets as select c.cs_id, c.cs_make, c.cs_code, c.cs_type, cst.cst_desc, c.cs_colour, cs.cost_id, cs.cost_cl_id, cs.cost_supp, o.or_id, o.or_supp, o.or_date, o.or_received, o.or_no, orst.orst_desc as order_state, co.co_id, co.co_name, co.co_person, co.co_tel, co.co_mobile, co.co_fax, co.co_email, co.co_type, cl.cl_desc, c.cs_comments, cs.cost_comments from consumables c, orders o, cons_stock cs, contacts co, cons_locations cl, cons_types cst, order_states orst where cs.cost_cs_id = c.cs_id and cs.cost_or_id = o.or_id and c.cs_type = cst.cst_id and o.or_supp = co.co_id and o.or_state = orst.orst_id and cs.cost_cl_id = cl.cl_id; create view order_summary as select c.cs_id, cs.count as qty, c.cs_make, c.cs_code, c.cs_type, cst.cst_desc, c.cs_colour, o.or_id, o.or_supp, o.or_date, o.or_received, o.or_no, orst.orst_desc as order_state, co.co_id, co.co_name, co.co_person, co.co_tel, co.co_mobile, co.co_fax, co.co_email, co.co_type, c.cs_comments from consumables c, orders o, (select cost_cs_id, cost_or_id, count(cost_cs_id) from cons_stock where cost_or_id is not null group by cost_cs_id, cost_or_id ) cs, contacts co, cons_locations cl, cons_types cst, order_states orst where cs.cost_cs_id = c.cs_id and cs.cost_or_id = o.or_id and c.cs_type = cst.cst_id and o.or_supp = co.co_id and o.or_state = orst.orst_id; hardware=# select cs_id, cs_make, cs_code, cst_desc, cs_colour, cost_id, or_id from order_dets hardware-# order by or_id, cs_id, cost_id; cs_id | cs_make | cs_code |cst_desc | cs_colour | cost_id | or_id ---+-+-+-+---+-+--- 31 | Kyocera | TK-50H | Toner cartridge | BLACK | 72 | 1 31 | Kyocera | TK-50H | Toner cartridge | BLACK | 73 | 1 31 | Kyocera | TK-50H | Toner cartridge | BLACK | 74 | 1 32 | HP | C3903A | Toner cartridge | BLACK | 79 | 1 32 | HP | C3903A | Toner cartridge | BLACK | 80 | 1 17 | Epson | T0442 | Ink cartridge | CYAN | 82 | 2 24 | Epson | S050097 | Toner cartridge | YELLOW| 85 | 2 29 | Brother | TN-6300 | Toner cartridge | BLACK | 86 | 2 32 | HP | C3903A | Toner cartridge | BLACK | 87 | 2 33 | PHILIPS | PFA331 | Ink Film| BLACK | 83 | 2 33 | PHILIPS | PFA331 | Ink Film| BLACK | 84 | 2 1 | HP | C4096A | Toner cartridge | BLACK | 90 | 3 1 | HP | C4096A | Toner cartridge | BLACK | 91 | 3 8 | HP | C6578D | Ink cartridge | C-M-Y | 88 | 3 9 | HP | C6615D | Ink cartridge | BLACK | 89 | 3 6 | HP | C8061X | Toner cartridge | BLACK | 95 | 6 16 | Epson | T0441 | Ink cartridge | BLACK | 92 | 6 18 | Epson | T0443 | Ink cartridge | MAGENTA | 93 | 6 19 | Epson | T0444 | Ink cartridge | YELLOW| 94 | 6 32 | HP | C3903A | Toner cartridge | BLACK | 96 | 6 32 | HP | C3903A | Toner cartridge | BLACK | 97 | 6 34 | SAMSUNG | SF-5100 | Ink Film| BLACK | 98 | 6 (22 rows) hardware=# select cs_id, cs_make, cs_code, cst_desc, cs_colour, qty, or_id from order_summary hardware-# order by or_id, cs_id; cs_id | cs_make | cs_code |cst_desc | cs_colour | qty | or_id ---+-+-+-+---+-+--- 31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1 31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1 31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1 31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1 31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1 31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1 31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1 32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1 32 |
Re: [SQL] working with schema
Christoph, First, is it safe for multi user? I mean maybe first user need working with D200402, second one need with D200403, if I do this in first user connection: SET search_path to D200402 ; does it affect to the second user search path? Second, I want it dinamic. So, if I want to using D200402, I just need to pass 'D200402' string in the argument of the function. Got my point? Thanks anyway, William >> >> Hi all, >> >> I'm just experimenting with schema usage. I'm going to use it as a fake >> 'multi-database' system. Is Postgresql support coding schema name using string >> variable so I can pass it with parameter? I'm give u an example: >> >> I have schema: D200401,D200402.D200403,D200404, etc. >> >> I've set my user just like the schema name, so who login with D200401 will be using >> D200401 schema. When someone using D200401 schema, they sometime want to access >> another schema, so in my thought I can use variable like this: >> >> sPointer='D200403' >> >> select * from sPointer.myTable -- Question: How to write it to work properly? >> >> Thanks >> >> >> William >> >> >SET search_path to D200401 ; >SET search_path to D200402 ; >... >should do the job. > >Regards, Christoph > > >---(end of broadcast)--- >TIP 4: Don't 'kill -9' the postmaster > Need a new email address that people can remember Check out the new EudoraMail at http://www.eudoramail.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] How do i extract a certain bit from a bigint column
Hello everyone In a table i have a column status of type bigint. I need to create a view of the table including all rows with bit 4 set (value 8). At the same time i need to exclude excludig all rows with bit 2 set. What is the syntax to extract those bits? I have tested get_bit(string, offset) but this requires a string, and not a bigint. Is there a function to Best regards, Mats BEGIN:VCARD VERSION:2.1 N:Sjöberg;Mats FN:Mats Sjöberg ORG:Cybernetics Business Decisions AB TEL;WORK;VOICE:3921 TEL;CELL;VOICE:0706 54 07 68 ADR;WORK:;;Rännilstäppan;ÅKERSBERGA;;184 91;SWEDEN LABEL;WORK;ENCODING=QUOTED-PRINTABLE:R=E4nnilst=E4ppan=0D=0A=C5KERSBERGA 184 91=0D=0ASWEDEN EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20040302T073452Z END:VCARD ---(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
Re: [SQL] How do i extract a certain bit from a bigint column
On Wednesday 12 May 2004 07:05 am, Mats Sjöberg wrote: > Hello everyone > In a table i have a column status of type bigint. > I need to create a view of the table including all rows with bit 4 set > (value 8). > At the same time i need to exclude excludig all rows with bit 2 set. > > What is the syntax to extract those bits? > I have tested get_bit(string, offset) but this requires a string, and not a > bigint. > Is there a function to The easiest way is to test for a bit using bitwise and: SELECT * FROM table WHERE (status & 2::BIGINT) <> 0 AND (status & 8::BIGINT) = 0; -miker ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How do i extract a certain bit from a bigint column
=?iso-8859-1?Q?Mats_Sj=F6berg?= <[EMAIL PROTECTED]> writes: > What is the syntax to extract those bits? At least in 7.4, you can do it the same way you'd do it in C: regression=# select ((47::bigint) >> 3) & 1; ?column? -- 1 (1 row) I'm not sure when the bigint >> and & operators got added, but \do would tell you quickly enough if they're in your version. Mind you that this is not going to be an especially fast solution, since these are not indexable operators. You might be better advised to rethink your data representation. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] working with schema
On Wed, 12 May 2004, William Anthony Lim wrote:
> Christoph,
>
> First, is it safe for multi user? I mean maybe first user need working with D200402,
> second one need with D200403, if I do this in first user connection:
>
> SET search_path to D200402 ;
>
> does it affect to the second user search path?
No, search paths are session vars.
> Second, I want it dinamic. So, if I want to using D200402, I just need to pass
> 'D200402' string in the argument of the function. Got my point?
You should be able to do it with dot notation:
postgres=# create schema a;
CREATE SCHEMA
postgres=# create schema b;
CREATE SCHEMA
postgres=# create table a.test (info text);
CREATE TABLE
postgres=# create table b.test (info text);
CREATE TABLE
postgres=# insert into a.test values ('abc');
INSERT 1400496 1
postgres=# insert into b.test values ('123');
INSERT 1400497 1
-- Now we try to look up the table without setting a search path and no
-- dot notation:
postgres=# select * from test;
ERROR: relation "test" does not exist
ERROR: relation "test" does not exist
-- Now we set the search path, notice the order:
postgres=# set search_path=public,a,b;
SET
postgres=# select * from test;
info
--
abc
(1 row)
-- Reverse the order of a and b
postgres=# set search_path=public,b,a;
SET
postgres=# select * from test;
info
--
123
(1 row)
-- now without a
postgres=# set search_path=public,b;
SET
postgres=# select * from test;
info
--
123
(1 row)
postgres=# set search_path=public,a;
SET
postgres=# select * from test;
info
--
abc
(1 row)
-- Now we use dot notation. first a, then b. Notice that
-- b, which isn't in our search path, works fine.
postgres=# select * from a.test;
info
--
abc
(1 row)
postgres=# select * from b.test;
info
--
123
(1 row)
>
> Thanks anyway,
>
> William
>
> >>
> >> Hi all,
> >>
> >> I'm just experimenting with schema usage. I'm going to use it as a fake
> >> 'multi-database' system. Is Postgresql support coding schema name using string
> >> variable so I can pass it with parameter? I'm give u an example:
> >>
> >> I have schema: D200401,D200402.D200403,D200404, etc.
> >>
> >> I've set my user just like the schema name, so who login with D200401 will be
> >> using D200401 schema. When someone using D200401 schema, they sometime want to
> >> access another schema, so in my thought I can use variable like this:
> >>
> >> sPointer='D200403'
> >>
> >> select * from sPointer.myTable -- Question: How to write it to work properly?
> >>
> >> Thanks
> >>
> >>
> >> William
> >>
> >>
> >SET search_path to D200401 ;
> >SET search_path to D200402 ;
> >...
> >should do the job.
> >
> >Regards, Christoph
> >
> >
> >---(end of broadcast)---
> >TIP 4: Don't 'kill -9' the postmaster
> >
>
>
>
> Need a new email address that people can remember
> Check out the new EudoraMail at
> http://www.eudoramail.com
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
solved Re: [SQL] view problem - too many rows out
On Wednesday 12 May 2004 11:02 am, Gary Stainburn wrote: > Hi folks. > [snip] > create view order_summary as > select > c.cs_id, cs.count as qty, c.cs_make, c.cs_code, c.cs_type, > cst.cst_desc, c.cs_colour, > o.or_id, > o.or_supp, o.or_date, o.or_received, o.or_no, orst.orst_desc as > order_state, > co.co_id, co.co_name, co.co_person, co.co_tel, > co.co_mobile, co.co_fax, co.co_email, co.co_type, > c.cs_comments > from consumables c, orders o, > (select cost_cs_id, cost_or_id, count(cost_cs_id) from cons_stock >where cost_or_id is not null >group by cost_cs_id, cost_or_id > ) cs, contacts co, > cons_locations cl, cons_types cst, order_states orst cons_locations shouldn't have been there > where cs.cost_cs_id = c.cs_id > and cs.cost_or_id = o.or_id > and c.cs_type = cst.cst_id > and o.or_supp = co.co_id > and o.or_state = orst.orst_id; [snip] -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] where is this problem (trigger)
I fire this trigger whenever my client updates a row
in the db:
CREATE FUNCTION notify_jobinfo() RETURNS "trigger"
AS '
BEGIN
EXECUTE ''NOTIFY ''||TG_RELNAME||''_''||NEW.jobnumber;
RETURN NEW;
END
'
LANGUAGE plpgsql;
CREATE TRIGGER notify_jobinfo
AFTER UPDATE ON jobinfo
FOR EACH ROW
EXECUTE PROCEDURE notify_jobinfo();
CREATE TABLE jobinfo (
acode text,
jobnumber text DEFAULT
nextval('public.jobinfo_seq'::text),
creationdate date DEFAULT now(),
shortdescription text,
projectcode text,
holdnumber text,
insertioninfo text,
jobtitle text,
insertiondate text,
iscomplete boolean DEFAULT false,
isbilled boolean DEFAULT false,
CONSTRAINT "$1" CHECK ((jobnumber <> ''::text))
);
My problem is that I have had to import legacy data
for the jobnumber column. My client was originally
using an excel file. they were free to do as they
pleased and there are jobnumbers like '1041.01'
'1041.02'.
I can not seem to update these rows because of the dot
in the jobnumber field. I have found that I can change
the dot to an underscore but I thought I would ask if
there is a better solution.
here is the error:
UPDATE jobinfo SET isbilled = false WHERE jobnumber =
'1162.01';
ERROR: syntax error at or near ".01" at character 20
CONTEXT: PL/pgSQL function "notify_jobinfo" line 2 at
execute statement
Ted
__
Do you Yahoo!?
Yahoo! Movies - Buy advance tickets for 'Shrek 2'
http://movies.yahoo.com/showtimes/movie?mid=1808405861
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] How do i extract a certain bit from a bigint column
On Wed, May 12, 2004 at 08:17:39 -0400, Mike Rylander <[EMAIL PROTECTED]> wrote: > > The easiest way is to test for a bit using bitwise and: > > SELECT * FROM table WHERE (status & 2::BIGINT) <> 0 AND (status & 8::BIGINT) = > 0; The following will probably be a bit faster: SELECT * FROM table WHERE (status & 10::BIGINT) = 8; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Index not used - now me
I know there have been dozens of threads on this subject and I have searched the archives well (I hope at least), but still ... I have select version(); version -- PostgreSQL 7.3.4 on hppa-hp-hpux10.20, compiled by GCC 2.8.1 show enable_seqscan ; enable_seqscan off \d ParDef_DimRange Table "public.pardef_dimrange" Column | Type | Modifiers ---+--+--- primary_key | integer | not null dim_pointer | smallint | not null dimensions_nr | smallint | not null first | smallint | not null last | smallint | not null max_range | smallint | not null Indexes: pd_dptr_index btree (dim_pointer), pd_pkey_index btree (primary_key) explain analyze SELECT Dim_Pointer,Dimensions_Nr,First,Last FROM ParDef_DimRange WHERE Dim_Pointer = 162::smallintORDER BY Dim_Pointer,Dimensions_Nr; QUERY PLAN --- Sort (cost=7.02..7.03 rows=2 width=8) (actual time=0.72..0.75 rows=2 loops=1) Sort Key: dim_pointer, dimensions_nr -> Index Scan using pd_dptr_index on pardef_dimrange (cost=0.00..7.01 rows=2 width=8) (actual time=0.20..0.28 rows=2 loops=1) Index Cond: (dim_pointer = 162::smallint) Total runtime: 1.24 msec excellent, but explain analyze SELECT Dim_Pointer,Dimensions_Nr,First,Last FROM ParDef_DimRange WHERE Dim_Pointer = 162ORDER BY Dim_Pointer,Dimensions_Nr; QUERY PLAN --- Sort (cost=10062.22..10062.23 rows=2 width=8) (actual time=32.44..32.46 rows=2 loops=1) Sort Key: dim_pointer, dimensions_nr -> Seq Scan on pardef_dimrange (cost=1.00..10062.21 rows=2 width=8) (actual time=11.06..31.93 rows=2 loops=1) Filter: (dim_pointer = 162) Total runtime: 32.79 msec That's not nice. Will this go away on 7.4? \d Transfer_ModRange Table "public.transfer_modrange" Column | Type | Modifiers +--+--- module_pointer | smallint | not null from_module| smallint | not null to_module | smallint | not null primary_key| integer | not null Indexes: tmr_primkey_index btree (primary_key) explain analyze SELECT Module_Pointer FROM Transfer_ModRange WHERE Primary_Key = 13 AND From_Module <= 2 AND To_Module >= 2 ; QUERY PLAN -- Index Scan using tmr_primkey_index on transfer_modrange (cost=0.00..115.09 rows=14 width=2) (actual time=2.11..2.11 rows=0 loops=1) Index Cond: (primary_key = 13) Filter: ((from_module <= 2) AND (to_module >= 2)) Total runtime: 2.46 msec Now set enable_seqscan to on ; explain analyze SELECT Module_Pointer FROM Transfer_ModRange WHERE Primary_Key = 13 AND From_Module <= 2 AND To_Module >= 2 ; QUERY PLAN -- Seq Scan on transfer_modrange (cost=0.00..104.93 rows=14 width=2) (actual time=45.91..45.91 rows=0 loops=1) Filter: ((primary_key = 13) AND (from_module <= 2) AND (to_module >= 2)) Total runtime: 46.19 msec That's odd. May I please have an explanation for this. Probably I should mention both tables have far less than 10.000 tuples. VACUUM and ANALYZE was done just before. TIA Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] working with schema
There is something still annoying me Scott..
eg: I want to have function that take a string argument that indicates which schema i
want to use.
create function testf(varchar)
returns ...
..
..
..
..
My question is how to use the argument in the function, maybe looks like:
select * from $1.test
or
set search_path to $1
select * from test
or maybe I defined a string variable to hold it,
workschema='D200402'
select * from workschema.test
Do they work?
Thanks,
William
>On Wed, 12 May 2004, William Anthony Lim wrote:
>
>> Christoph,
>>
>> First, is it safe for multi user? I mean maybe first user need working with
>> D200402, second one need with D200403, if I do this in first user connection:
>>
>> SET search_path to D200402 ;
>>
>> does it affect to the second user search path?
>
>No, search paths are session vars.
>
>> Second, I want it dinamic. So, if I want to using D200402, I just need to pass
>> 'D200402' string in the argument of the function. Got my point?
>
>You should be able to do it with dot notation:
>
>postgres=# create schema a;
>CREATE SCHEMA
>postgres=# create schema b;
>CREATE SCHEMA
>postgres=# create table a.test (info text);
>CREATE TABLE
>postgres=# create table b.test (info text);
>CREATE TABLE
>postgres=# insert into a.test values ('abc');
>INSERT 1400496 1
>postgres=# insert into b.test values ('123');
>INSERT 1400497 1
>
>-- Now we try to look up the table without setting a search path and no
>-- dot notation:
>
>postgres=# select * from test;
>ERROR: relation "test" does not exist
>ERROR: relation "test" does not exist
>
>-- Now we set the search path, notice the order:
>
>postgres=# set search_path=public,a,b;
>SET
>postgres=# select * from test;
> info
>--
> abc
>(1 row)
>
>-- Reverse the order of a and b
>
>postgres=# set search_path=public,b,a;
>SET
>postgres=# select * from test;
> info
>--
> 123
>(1 row)
>
>-- now without a
>
>postgres=# set search_path=public,b;
>SET
>postgres=# select * from test;
> info
>--
> 123
>(1 row)
>
>postgres=# set search_path=public,a;
>SET
>postgres=# select * from test;
> info
>--
> abc
>(1 row)
>
>-- Now we use dot notation. first a, then b. Notice that
>-- b, which isn't in our search path, works fine.
>
>postgres=# select * from a.test;
> info
>--
> abc
>(1 row)
>
>postgres=# select * from b.test;
> info
>--
> 123
>(1 row)
>
>
>>
>> Thanks anyway,
>>
>> William
>>
>> >>
>> >> Hi all,
>> >>
>> >> I'm just experimenting with schema usage. I'm going to use it as a fake
>> >> 'multi-database' system. Is Postgresql support coding schema name using string
>> >> variable so I can pass it with parameter? I'm give u an example:
>> >>
>> >> I have schema: D200401,D200402.D200403,D200404, etc.
>> >>
>> >> I've set my user just like the schema name, so who login with D200401 will be
>> >> using D200401 schema. When someone using D200401 schema, they sometime want to
>> >> access another schema, so in my thought I can use variable like this:
>> >>
>> >> sPointer='D200403'
>> >>
>> >> select * from sPointer.myTable -- Question: How to write it to work properly?
>> >>
>> >> Thanks
>> >>
>> >>
>> >> William
>> >>
>> >>
>> >SET search_path to D200401 ;
>> >SET search_path to D200402 ;
>> >...
>> >should do the job.
>> >
>> >Regards, Christoph
>> >
>> >
>> >---(end of broadcast)---
>> >TIP 4: Don't 'kill -9' the postmaster
>> >
>>
>>
>>
>> Need a new email address that people can remember
>> Check out the new EudoraMail at
>> http://www.eudoramail.com
>>
>> ---(end of broadcast)---
>> TIP 6: Have you searched our list archives?
>>
>>http://archives.postgresql.org
>>
>
>
>---(end of broadcast)---
>TIP 7: don't forget to increase your free space map settings
>
Need a new email address that people can remember
Check out the new EudoraMail at
http://www.eudoramail.com
---(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] \set
I am not talking about SET, I am talking about \set command.
Yes, I understand, plpgsql can work around it, but I think postgresql
should have a simple way to do it.
E.g.
Db>\set AAA 'whatever'
Db>\set
You will see AAA associate with 'whatever', it's an internal variable,
but how could I use it in my SQL query?
Thanks anyway.
Jie Liang
-Original Message-
From: Christian Kratzer [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 12, 2004 3:13 AM
To: Jie Liang
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [SQL] \set
Hi,
On Tue, 11 May 2004, Jie Liang wrote:
> Hi,
> How to use an internal variable?
> Original question was how to set a variable in postgresql?
> If I want to set a variable like start_date='2004-05-10';
> How could I use it in my SQL statement?
> E.g.
>
> Db> set start_date '2004-05-10'
> Db> select start_date as 'start date';
>
> It's not executable!
from doc/postgresql/html/sql-set.html
--snipp--
Synopsis
SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' |
DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }
...
...
...
name
Name of a settable run-time parameter.
Available parameters are documented in Section 16.4 and
below.
--snipp--
that is you can only use SET to change specific predefined parameters.
It does not say you could use SET to store other data local to the
database session.
As far as I know there are no session local variables in postgresql. I
could use something like this myself. The only workaround I know of
to have data local to a session is to create a temporary table for the
data.
There are local variables in plpgsql if you are just looking for local
variables.
Greetings
Christian
--
Christian Kratzer [EMAIL PROTECTED]
CK Software GmbHhttp://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] [SQL] \set
"Jie Liang" <[EMAIL PROTECTED]> writes: > You will see AAA associate with 'whatever', it's an internal variable, > but how could I use it in my SQL query? regression=# \set AAA 'whatever' regression=# select :AAA; ERROR: column "whatever" does not exist regression=# \set AAA '\'whatever\'' regression=# select :AAA; ?column? -- whatever (1 row) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] [SQL] \set
Thank you, Tom. Jie -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 4:06 PM To: Jie Liang Cc: Christian Kratzer; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [ADMIN] [SQL] \set "Jie Liang" <[EMAIL PROTECTED]> writes: > You will see AAA associate with 'whatever', it's an internal variable, > but how could I use it in my SQL query? regression=# \set AAA 'whatever' regression=# select :AAA; ERROR: column "whatever" does not exist regression=# \set AAA '\'whatever\'' regression=# select :AAA; ?column? -- whatever (1 row) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
