[SQL] How to use BYTEA type?
Greetings folks,
Please could a kind soul tell be how to extract data from a BYTEA type of
field into a file, or better still tell me where I can find some doco?
This is the table:-
create table fax_info ( id serial, comment text, image bytea) ;
This appears to work ( no erorr messages ):-
chris=# insert into fax_info ( comment, image ) values
( 'Faking it with a computer', byteain ('picture.pgm.gz'));
INSERT 18772 1
Is this correct?
Now, how do I get my picture out again?
TNX 10^6
Sincerely etc.
Christopher Sawtell.
---(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] transposing data for a view
H Jeremy Bockholt schreef: > I have a generalized table: > > scanid | region | volume > - > 1 A 34.4 > 1 B 32.1 > 1 C 29.1 > 2 A 32.4 > 2 B 33.2 > 2 C 35.6 > . > . > . > > I want to create a flattened out view that looks like the following: > > scanid | A_volume | B_volume | C_volume > > 134.4 32.1 29.1 > 232.4 33.2 35.6 > . > . > . > > How do I correctly/efficiently construct a psql query to > pivot/transpose the data? I am using postgreSQL version 7.0.x > > thanks, > Jeremy Try This: select region, sum(a_volume) AS a_volume, sum(b_volume) AS b_volume, sum(c_volume) AS c_volume from ( select scanid, volume AS a_volume, 0 AS b_volume, 0 AS c_volume from mytable where region = A UNION select scanid, 0 AS a_volume, volume AS b_volume, 0 AS c_volume from mytable where region = B UNION select scanid, 0 AS a_volume, 0 AS b_volume, volume AS c_volume from mytable where region = C ) tmp (you might have to specifically typecast the zero's) It would probably also be possible using CASE-statements. This is just _one_ idea. Arian. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] View consistency
"David M. Richter" <[EMAIL PROTECTED]> writes: > How does postgres keep the views consistent to the according tables( if > the original table has been changed)? It doesn't have to; views are not materialized in Postgres. A view is just a rewrite rule, or macro. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] table permissions
Hi everybody, I can't seem to figure this one out. I have a superuser root and a regular user oleg. I am trying to insert a new row in table Set, which references table Activity, being logged in as root. I get an error saying: activity: Permission denied. This is very weird because root has all the permissions on every table in the database. Even more strange is that as soon as I grant DELETE privilege to user oleg, the problem disappears and I can add a row to the Set table. Does anybody know how to solve this problem? Should I upgrade my PostgreSQL version? I am currently using v 7.1.2 thanks, Oleg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Aggregate binary AND
Hi, Is there an aggregate binary AND function in postgres? If not, is there a way to write your own aggregate functions? Examples? - James ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] transposing data for a view
--- James Orr <[EMAIL PROTECTED]> wrote: > I think he might be talking about mine. The region > values will not be > duplicated, the WHERE clause prevents it. If you are saying that I didn't read the original query closely enough, you're probably right. Unfortunately I deleted the original message, so I can't check that. > I kind of prefer my own query aesthetically, is it > as efficient internally? Someone more knowledgable will have to answer that: though I would guess that working with three joined tables would slow things down somewhat. > > - James > __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com ---(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] How to use BYTEA type?
Christopher Sawtell wrote:
> Greetings folks,
>
> Please could a kind soul tell be how to extract data from a BYTEA type of
> field into a file, or better still tell me where I can find some doco?
Bruce has already written some minimal documentation which is in the 7.2
beta. I'm hoping to add to that prior to the 7.2 release
>
> This is the table:-
>
> create table fax_info ( id serial, comment text, image bytea) ;
>
> This appears to work ( no erorr messages ):-
>
> chris=# insert into fax_info ( comment, image ) values
> ( 'Faking it with a computer', byteain ('picture.pgm.gz'));
> INSERT 18772 1
>
> Is this correct?
No. You'll end up with literally the text 'picture.pgm.gz' in image.
What you need to do is escape 3 special characters in your application,
and then insert the escaped string directly. How exactly you do that
will vary depending on your application layer language. For example, in
PHP, you would do something like:
$image_name = "/tmp/myimage.jpg";
$fp = fopen($image_name,"r");
$image = fread($fp, filesize($image_name));
fclose($fp);
$esc_string = bytea_esc($image);
$sql = "insert into fax_info(comment,image) ";
$sql .= "values ('my image comment','$esc_string')";
$rs = pg_exec($conn, $sql);
where bytea_esc() is the function that escapes the special characters.
The three are ASCII 0, ASCII 39 (single quote), and ASCII 92 (single
backslash). In 7.2 there is a libpq function which can be called from
your C program to do the escaping, but for now, and in other programming
environments you may have to write your own. I have seen posts
indicating that the Perl DBI library for Postgres does have this
function already.
The escaping is a little tricky, and again varies depending on your
programming environment. When the string *reaches PostgreSQL*, it needs
to be escaped like this:
ASCII 0 ==> \\000
ASCII 39 ==>\' or \\047
ASCII 92 ==>or \\134
So an input string like 'helloworld' would wind up being
inserted like (where is a single 0 byte):
insert into foo(mybytea) values('hello\\000world');
As I said, the escaped string in your programming environment may need
to be different. In PHP for example, one set of backslashes is striped
by the PHP language parser (so \\ becomes \), so the actual function I
use looks like:
function bytea_esc($ct)
{
$buf = "";
for ($i = 0; $i < strlen($ct); $i++)
{
if (ord($ct[$i]) == 0)
$buf .= "000";
else if (ord($ct[$i]) == 39)
$buf .= "047";
else if (ord($ct[$i]) == 92)
$buf .= "134";
else
$buf .= $ct[$i];
}
return $buf;
}
>
> Now, how do I get my picture out again?
>
To get it back out, you query it out the same as any other field. The
catch is that all "non-printable" characters (which is quite a few more
than the three above) are returned to you escaped, i.e. ASCII 255 will
be returned as '\377'. So again you need to unescape the returned string
using your application programming language. In PHP there is a native
function which works great: stripcslashes(). So to complete the PHP example:
$sql = "select image from fax_info ";
$sql .= "where serial = 1";
$rs = pg_exec($conn, $sql);
$image = stripcslashes(pg_result($rs,0,0));
header("content-type: image/jpeg");
echo $image;
Hope this helps,
Joe
---(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] Aggregate binary AND
This worked for us in 7.something (don't need it anymore, but it should still work). You'll want to create one for INT8, INT2, etc.. if you are going to use those as well... CREATE AGGREGATE aggr_bitand ( BASETYPE = INT4, SFUNC1 = int4and, STYPE1 = INT4); On Thu, 1 Nov 2001, James Orr wrote: > Hi, > > Is there an aggregate binary AND function in postgres? If not, is there a > way to write your own aggregate functions? Examples? > > - James > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(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] Sorry for blocking email
My spam filter was misconfigured and I bounced back some messages I shouldn't have. Sorry. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Error 34526 - cache lookup failed
Always I execute the SQL insert into questionarios (codigo, descricao) values (10, 'Basico') happen the error below. Do Anybody know what is happened? Error - /var/www/html/tecWEB/pgadmin/sql.php -- Line: 107 PostgreSQL said: ERROR: fmgr_info: function 34526: cache lookup failed Your query: insert into questionarios (codigo, descricao) values (10, 'Basico') Thanks Sandro Joel Eller Analista/Programador www.tecsoft.com.br [EMAIL PROTECTED] ---(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] Aggregate binary AND
"James Orr" <[EMAIL PROTECTED]> writes: > Is there an aggregate binary AND function in postgres? If not, is there a > way to write your own aggregate functions? Examples? The aggregate would be trivial given an underlying two-argument AND function to build it from: regression=# create aggregate booland ( basetype = bool, regression(# sfunc = booland, stype = bool, regression(# initcond = 'true' ); ERROR: AggregateCreate: function 'booland(bool, bool)' does not exist Unfortunately, it seems no one has bothered to create a functional form of AND (the keyword-AND operator isn't a function). I'll leave that part as an exercise for the student ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Error 34526 - cache lookup failed
On Thu, 1 Nov 2001, Sandro Joel Eller wrote: > Always I execute the SQL > > insert into questionarios (codigo, descricao) values (10, 'Basico') > > happen the error below. Do Anybody know what is happened? > > Error - /var/www/html/tecWEB/pgadmin/sql.php -- Line: 107 > PostgreSQL said: ERROR: fmgr_info: function 34526: cache lookup failed > Your query: > insert into questionarios (codigo, descricao) values (10, 'Basico') Do you have any triggers or rules or such on questionarios? If so, you may have deleted a function referenced by one of these (possibly to recreate it) without recreating the trigger or rule associated. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PL/pgSQL syntax for strings
On Thu, Nov 01, 2001 at 04:48:29PM +, Edward Grabczewski wrote:
>
> DROP FUNCTION threedpoint (float,float,float, float,float,float);
> CREATE FUNCTION threedpoint (float,float,float,float,float,float)
> RETURNS text
> AS 'DECLARE
> x1 ALIAS FOR $1;
> y1 ALIAS FOR $2;
> z1 ALIAS FOR $3;
> x2 ALIAS FOR $4;
> y2 ALIAS FOR $5;
> z2 ALIAS FOR $6;
> BEGIN
> INSERT INTO rtest(xz,yz,xy)
> VALUES ( \'(0,2), (1,3)\',
> \'(1,2), (2,3)\',
> \'(0,1), (1,2)\');
> RETURN null;
> END;'
> LANGUAGE 'plpgsql';
>
> SELECT threedpoint(100,200,300,400,500,600);
Why do you pass all those variables to the function if you don't use them?
It doesn't make any sense to me.
As the documentation for PL/pgSQL clearly states with several examples,
you have to double the quotes in strings>
INSERT INTO rtest(xz,yz,xy)
VALUES (''(0,2), (1,3)'',
''(1,2), (2,3)'',
''(0,1), (1,2)'');
I don't know if the \' way of escaping quotes works. I never tried within
a PL/pgSQL function. My guess is that it doesn't.
-Roberto
--
+| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net
http://www.sdl.usu.edu - Space Dynamics Lab, Developer
My inferiority complexes aren't as good as yours.
---(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] transposing data for a view
Jeff,
> SELECT
>scanid,
>sum ( CASE WHEN region = 'A' THEN volume ELSE NULL
> )
> AS A_volume,
>sum ( CASE WHEN region = 'B' THEN volume ELSE NULL
> )
> AS B_volume,
>sum ( CASE WHEN region = 'C' THEN volume ELSE NULL
> )
> AS C_volume
> FROM table
> GROUP BY scanid;
Hey, that's an elegant solution to doing it in 7.0.3. I hadn't thought
of it. Jeremy, never mind what I said about being forced to upgrade.
Upgrading *would* still be a good idea, of course.
Of couse, it's only *half* a solution. Your query will result in:
scanid A_volume B_volume C_volume
1 34.5
1 55.1
1 12.3
2 11.1
etc.
For the second half of the solution, Jeremy needs to create the above as
a view ('volume_rollup_1') and apply this second view:
SELECT scanid, SUM(A_volume) as A_volume, SUM(B_Volume) as B_Volume,
SUM(C_volume) as C_volume
FROM volume_rollup_1;
This will give Jeremy the "pivot" grid he's looking for.
> BTW, I don't believe the self-join approach proposed
> earlier will work, because joining on "scanid" will
> create a cartesian type join where the region values
> will be duplicated (multiplicated!).
Not if you're talking about my query, they won't. I use that query form
in many projects to create roll-ups; it's the "best" SQL92 approach to
the "pivot table" problem. However, it will not work in 7.0.3.
-Josh
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] transposing data for a view
Jeremy, > I see how your idea could work--the only thing I would change would > be region needs to be scanid on the first line; however, > I'm using PostgreSQL v7.03, so I can not do subqueries within FROM > clause. > > Other than creating a temporary table, is there a way to > restructure this solution to get around this limitation? No, you need to upgrade. What's the obstacle to using 7.1.3, anyway? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(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] transposing data for a view
Josh, I don't see how you got the result you quote below. My query sums the volume figures, grouping by scanid: that worked as expected (one line per scanid) on my system when I tested it (version 7.1.2, though I don't think that matters). Jeff --- Josh Berkus <[EMAIL PROTECTED]> wrote: > Of couse, it's only *half* a solution. Your query > will result in: > > scanidA_volume B_volume C_volume > 1 34.5 > 1 55.1 > 1 12.3 > 2 11.1 > etc. > __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to use BYTEA type?
On Thu, 1 Nov 2001, Radu-Adrian Popescu wrote:
> Doing
>
> template1=> SELECT proname from pg_proc ;
> proname
>
> boolin
> boolout
> byteain
> byteaout
>
> you can see there is a byteaout function also. However, these are
> undocumented in the interactive docs, which is a shame indeed. I only
> learned of byteain/out after reading your email.
>
> If some of the postgresql guys could point the location of the
> documentation of undocumented functions :-) that'd be great.
AFAIK, the in and out functions are *not* meant to be called by
users. They're used by the system and I think they technically
take pointer values. To use bytea correctly, I believe you are
supposed to do some escaping and place the value into the
insert statement.
insert into table values ('\011\005\\000afkajdd\011');
(note the double backslash on the null byte). And you should
get the value back from a straight select on the column I believe.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] problem in group by
I have a table masterauthor which has columns as AUTHOR NAME and ID ( ID is primary key ) and a second TABLE*(sellTable ) having the numbers books of the author which are sold i mean the say that the all those authors whose books are being sold are entered in the second table I have to display author's name and id from the forst list grouping the author who has sold the book once RegardsAmin * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com
Re: [SQL] transposing data for a view
Jeff, > I don't see how you got the result you quote below. > My query sums the volume figures, grouping by scanid: > that worked as expected (one line per scanid) on my > system when I tested it (version 7.1.2, though I don't > think that matters). Lemme try it Yes, you're right. Sorry! Disregard my commentary about the second view. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] PL/pgSQL syntax for strings
Could someone please tell me the correct syntax for the following problem in plpgsql: The following expression code sample works but now (for example) I'd like to subsitute the variables $1 and $2 for the numbers 0 and 2 in the following expression: INSERT INTO rtest(xz,yz,xy) VALUES ( \'(0,2), (1,3)\', \'(1,2), (2,3)\', \'(0,1), (1,2)\'); What's the correct way of represinting the strings? A complete code example is below. It's to do with the usage of R-tree indexing. Eddy [EMAIL PROTECTED] === DROP TABLE rtest; DROP INDEX rtest_xz_index; DROP INDEX rtest_yz_index; DROP INDEX rtest_xy_index; CREATE TABLE rtest ( xz BOX, yz BOX, xy BOX); CREATE INDEX rtest_xz_index ON rtest USING RTREE (xz bigbox_ops); CREATE INDEX rtest_yz_index ON rtest USING RTREE (yz bigbox_ops); CREATE INDEX rtest_xy_index ON rtest USING RTREE (xy bigbox_ops); DROP FUNCTION threedpoint (float,float,float, float,float,float); CREATE FUNCTION threedpoint (float,float,float,float,float,float) RETURNS text AS 'DECLARE x1 ALIAS FOR $1; y1 ALIAS FOR $2; z1 ALIAS FOR $3; x2 ALIAS FOR $4; y2 ALIAS FOR $5; z2 ALIAS FOR $6; BEGIN INSERT INTO rtest(xz,yz,xy) VALUES ( \'(0,2), (1,3)\', \'(1,2), (2,3)\', \'(0,1), (1,2)\'); RETURN null; END;' LANGUAGE 'plpgsql'; SELECT threedpoint(100,200,300,400,500,600); SELECT * FROM rtest ORDER BY xz USING <<; SELECT xz, yz, xy FROM rtest WHERE xz @ '(1.0,3.0),(0.0,2.0)'::box AND yz @ '(2.0,3.0),(1.0,2.0)'::box AND xy @ '(1.0,2.0),(0.0,1.0)'::box ORDER BY xz USING <<; -- Eddy Grabczewski [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] transposing data for a view
> > BTW, I don't believe the self-join approach proposed > > earlier will work, because joining on "scanid" will > > create a cartesian type join where the region values > > will be duplicated (multiplicated!). > > Not if you're talking about my query, they won't. I use that query form > in many projects to create roll-ups; it's the "best" SQL92 approach to > the "pivot table" problem. However, it will not work in 7.0.3. I think he might be talking about mine. The region values will not be duplicated, the WHERE clause prevents it. I kind of prefer my own query aesthetically, is it as efficient internally? - James ---(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] Recursive select
The usual example of a tree structure in SQL books is called an adjacency list model and it looks like this: CREATE TABLE Personnel (emp CHAR(10) NOT NULL PRIMARY KEY, boss CHAR(10) DEFAULT NULL REFERENCES Personnel(emp), salary DECIMAL(6,2) NOT NULL DEFAULT 100.00); Personnel emp boss salary === 'Albert' 'NULL'1000.00 'Bert''Albert' 900.00 'Chuck' 'Albert' 900.00 'Donna' 'Chuck'800.00 'Eddie' 'Chuck'700.00 'Fred''Chuck'600.00 Another way of representing trees is to show them as nested sets. Since SQL is a set oriented language, this is a better model than the usual adjacency list approach you see in most text books. Let us define a simple Personnel table like this, ignoring the left (lft) and right (rgt) columns for now. This problem is always given with a column for the employee and one for his boss in the textbooks. This table without the lft and rgt columns is called the adjacency list model, after the graph theory technique of the same name; the pairs of nodes are adjacent to each other. CREATE TABLE Personnel (emp CHAR(10) NOT NULL PRIMARY KEY, lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1), CONSTRAINT order_okay CHECK (lft < rgt) ); Personnel emp lft rgt == 'Albert' 1 12 'Bert'23 'Chuck' 4 11 'Donna' 56 'Eddie' 78 'Fred'9 10 The organizational chart would look like this as a directed graph: Albert (1,12) /\ /\ Bert (2,3)Chuck (4,11) /| \ / | \ /| \ / | \ Donna (5,6) Eddie (7,8) Fred (9,10) The first table is denormalized in several ways. We are modeling both the personnel and the organizational chart in one table. But for the sake of saving space, pretend that the names are job titles and that we have another table which describes the personnel that hold those positions. Another problem with the adjacency list model is that the boss and employee columns are the same kind of thing (i.e. names of personnel), and therefore should be shown in only one column in a normalized table. To prove that this is not normalized, assume that "Chuck" changes his name to "Charles"; you have to change his name in both columns and several places. The defining characteristic of a normalized table is that you have one fact, one place, one time. The final problem is that the adjacency list model does not model subordination. Authority flows downhill in a hierarchy, but If I fire Chuck, I disconnect all of his subordinates from Albert. There are situations (i.e. water pipes) where this is true, but that is not the expected situation in this case. To show a tree as nested sets, replace the nodes with ovals, then nest subordinate ovals inside each other. The root will be the largest oval and will contain every other node. The leaf nodes will be the innermost ovals with nothing else inside them and the nesting will show the hierarchical relationship. The rgt and lft columns (I cannot use the reserved words LEFT and RIGHT in SQL) are what shows the nesting. If that mental model does not work, then imagine a little worm crawling anti-clockwise along the tree. Every time he gets to the left or right side of a node, he numbers it. The worm stops when he gets all the way around the tree and back to the top. This is a natural way to model a parts explosion, since a final assembly is made of physically nested assemblies that final break down into separate parts. At this point, the boss column is both redundant and denormalized, so it can be dropped. Also, note that the tree structure can be kept in one table and all the information about a node can be put in a second table and they can be joined on employee number for queries. To convert the graph into a nested sets model think of a little worm crawling along the tree. The worm starts at the top, the root, makes a complete trip around the tree. When he comes to a node, he puts a number in the cell on the side that he is visiting and increments his counter. Each node will get two numbers, one of the right side and one for the left. Computer Science majors will recognize this as a modified preorder tree traversal algorithm. Finally, drop the unneeded Personnel.boss column which used to represent the edges of a graph. This has some predictable results that we can use for building queries. The root is always (left = 1, right = 2 * (SELECT COUNT(*) FROM TreeTable)); leaf nodes always have (left + 1 = right); subtrees are defined by the BETWEEN predicate; etc. Here are two common queries which can be used to build others: 1. An employee and all their Supervisors, no matter how deep the tree. SELECT P2.* FROM Personnel AS P1
Re: [SQL] transposing data for a view
Hi, I see how your idea could work--the only thing I would change would be region needs to be scanid on the first line; however, I'm using PostgreSQL v7.03, so I can not do subqueries within FROM clause. Other than creating a temporary table, is there a way to restructure this solution to get around this limitation? thanks, jeremy Arian Prins <[EMAIL PROTECTED]> wrote in message news:<[EMAIL PROTECTED]>... > H Jeremy Bockholt schreef: > > > I have a generalized table: > > > > scanid | region | volume > > - > > 1 A 34.4 > > 1 B 32.1 > > 1 C 29.1 > > 2 A 32.4 > > 2 B 33.2 > > 2 C 35.6 > > . > > . > > . > > > > I want to create a flattened out view that looks like the following: > > > > scanid | A_volume | B_volume | C_volume > > > > 134.4 32.1 29.1 > > 232.4 33.2 35.6 > > . > > . > > . > > > > How do I correctly/efficiently construct a psql query to > > pivot/transpose the data? I am using postgreSQL version 7.0.x > > > > thanks, > > Jeremy > > Try This: > > select region, sum(a_volume) AS a_volume, > sum(b_volume) AS b_volume, > sum(c_volume) AS c_volume > from ( > select > scanid, > volume AS a_volume, > 0 AS b_volume, > 0 AS c_volume > from mytable > where region = A > UNION > select > scanid, > 0 AS a_volume, > volume AS b_volume, > 0 AS c_volume > from mytable > where region = B > UNION > select > scanid, >0 AS a_volume, > 0 AS b_volume, > volume AS c_volume > from mytable > where region = C > ) tmp > > (you might have to specifically typecast the zero's) > > It would probably also be possible using CASE-statements. This is just > _one_ idea. > > Arian. ---(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] How to use BYTEA type?
Doing
template1=> SELECT proname from pg_proc ;
proname
boolin
boolout
byteain
byteaout
you can see there is a byteaout function also. However, these are
undocumented in the interactive docs, which is a shame indeed. I only
learned of byteain/out after reading your email.
If some of the postgresql guys could point the location of the
documentation of undocumented functions :-) that'd be great.
Radu-Adrian Popescu
DBA/programmer at www.aldratech.com
Christopher Sawtell wrote:
>Greetings folks,
>
> Please could a kind soul tell be how to extract data from a BYTEA type of
>field into a file, or better still tell me where I can find some doco?
>
>This is the table:-
>
>create table fax_info ( id serial, comment text, image bytea) ;
>
>This appears to work ( no erorr messages ):-
>
>chris=# insert into fax_info ( comment, image ) values
>( 'Faking it with a computer', byteain ('picture.pgm.gz'));
>INSERT 18772 1
>
>Is this correct?
>
>Now, how do I get my picture out again?
>
>TNX 10^6
>
>Sincerely etc.
>
>Christopher Sawtell.
>
>---(end of broadcast)---
>TIP 2: you can get off all lists at once with the unregister command
>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] View consistency
Hello! Im using several views for the Usermanagment of a database. My question is: How does postgres keep the views consistent to the according tables( if the original table has been changed)? Is there a Rule? and how is the Rule invoked. With ON DELETE UPDATE INSERT of the original table? I didnt found any Rule in pg_rules. There should be a rule, because I cannot imagine another way wich tells the view that the table has been changed. The problem is: If updating the view takes too much time after every little manipulation, I couldnt use views for usermanagement because of performance problems. Any hints and facts? Thanks in advance David begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum/German Cancer Research Center;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-17024 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] View consistency
On Thu, 1 Nov 2001, David M. Richter wrote: > Hello! > > Im using several views for the Usermanagment of a database. > My question is: > > How does postgres keep the views consistent to the according tables( if > the original table has been changed)? AFAIK it's a select rule on the view that rewrites into the view expression. Nothing keeps it consistent because it's effectively just a macro for the view expression. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
