Re: [SQL] have you feel anything when you read this ?
Praescriptum: If my english is ugly and something is written unclear, please complaint, and i'll try to rephrase. anyway i am trying to be understood. I said >> WHY SHOULD I RETRIVE A SINGLE BYTEA FIELD IN A SEPARATE QUERY ??? Stephan Szabo wrote: What would you expect it to do given a single result format argument? If you want to propose a new function (set of functions) that have different behavior, make a coherent proposal. > Statements like it should > do X because I want it to aren't coherent proposals. AFAIK, they convert each value before put it to a result set. I propose to do the following convertion to the textual-form for bytea values: X->X where X is byte [0..255] Expect to get asked why bytea is special _Because each type is special._ And at the same time they made bytea MORE special than any other type. Look: every type has many representations for its values, some are obvious some are more usefull, some are less useful. they define very useful and obvious representations for all the types but BYTEA. (They call those representations "textual-form".) and the input of a value demands escaping (we all undersdand why) and for each type the following equality is TRUE: some_data == OUTPUT(INPUT(ESCAPE(some_data))) but for the BYTEA this equality is FALSE ! Why BYTEA is so special ? every value of every type is expected to be given to a client UNCHANGED. I expect a value of BYTEA to be unchaged too. why should integer be passed as a string given because it is not causing problems, as well as if it be passed in any other common form. And i ask you: why integer is actually passed as a string (decimal notation) ? why not to define your own unique more_sofisticated representation ? (as for bytea is defined.) And finally Why so special textual-form defined for bytea ? Why not to leave every byte unchanged, since user knows what kind of data he got. P.S. changing a format of a whole result-set is not a solution for a field-type-dependent problem. ---(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
[SQL] pgcrypto-crypt
dear all, i want to encrypt and decrypt one of the fields in my table (i.e-password field) i have searched and with the help of pgcrypto package, using function "crypt", i am able to encrypt my data, but there is nothing which i found to decrypt that same data, plz anybody give me the function to decrypt that encrypted value. plz reply asap-- Thanks & Regards,AkhileshDAV Institute of ManagementFaridabad(Haryana)GSM:-(+919891606064) (+911744293789)"FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"
Re: [SQL] SELECT composite type
Hi, Michael, Michael Burke wrote: > This statement works, but I don't want to duplicate the function call as this > should be unnecessary. Is this for aesthetic reasons (typing the query), or for speed reasons? If the latter one is true, then check that all functions are defined as "immutable" or, at least, "stable". Then the qery planner should flatten them to be called only once. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] have you feel anything when you read this ?
On Thu, 6 Apr 2006, Eugene E. wrote: > Praescriptum: > If my english is ugly and something is written unclear, please > complaint, and i'll try to rephrase. > anyway i am trying to be understood. > > > I said > > >> WHY SHOULD I RETRIVE A SINGLE BYTEA FIELD IN A SEPARATE QUERY ??? I didn't answer this because I didn't feel that it moved the argument forward, but... If you meant that you must retrieve them in a separate query, you're incorrect, since you *could* use the binary form for the others. I can't understand if you don't realize that there is one for all these various types, or that you just don't wish to use it (for example, I believe using %d on ntohl(value from pqgetvalue) or something similar will print your integer). If you are arguing that you don't *wish* to do use that binary form for the other values, I don't see how that's relevant until you've proven the rest of the argument (*). > Stephan Szabo wrote: > > > What would you expect it to do given a single result format argument? > > > > If you want to propose a new function (set of functions) that have > > different behavior, make a coherent proposal. > > Statements like it should > > do X because I want it to aren't coherent proposals. > > AFAIK, they convert each value before put it to a result set. > I propose to do the following convertion to the textual-form for bytea > values: > X->X where X is byte [0..255] Okay, now pass that to strcmp or a %s format. AFAIK, the "textual-form" of values is meant to be a c-string. "ab\0cd\0" is not a c-string containing ab\0cd, it's a c-string containing ab. > > Expect to get asked > > why bytea is special > > _Because each type is special._ > > And at the same time they made bytea MORE special than any other type. I don't think that it's appreciably more special. > Look: > every type has many representations for its values, some are obvious > some are more usefull, some are less useful. > they define very useful and obvious representations for all the types > but BYTEA. There are two representations of (at least most) types. There's a binary format and a textual format. > (They call those representations "textual-form".) I think I don't exactly agree with this description, but I'm unclear exactly what you're saying. Are you saying that textual-form is the useful representation, or are you saying that textual-form is the representation and it is useful? > and the input of a value demands escaping (we all undersdand why) > and for each type the following equality is TRUE: > > some_data == OUTPUT(INPUT(ESCAPE(some_data))) > > but for the BYTEA this equality is FALSE ! > Why BYTEA is so special ? > > every value of every type is expected to be given to a client UNCHANGED. This is already false AFAICS. Leading or trailing spaces on a string containing integer get trimmed during the input for example, the string format of date comes back in a particular but other input formats are supported. I don't think the above equality is valid for textual representation. In addition, input could be binary and output textual or the other way around, in some_data is different on both sides. There's no reason that you can't be passing an integer that way. > I expect a value of BYTEA to be unchaged too. I think (as above) that your perception of the problem isn't correct. > > why should integer be passed as a string given > > because it is not causing problems, as well as if it be passed in any > other common form. > And i ask you: > why integer is actually passed as a string (decimal notation) ? It's not always. It can be, just as bytea can be passed as a string needing escaping, however it can be passed as effectively a binary blob containing an integer value (in network order I believe) just as bytea can be passed as a binary blob. > why not to define your own unique more_sofisticated representation ? > (as for bytea is defined.) AFAICS, there is one, the binary format for integer. > And finally > Why so special textual-form defined for bytea ? > Why not to leave every byte unchanged, since user knows what kind of > data he got. I think this is mostly answered by the above with a little bit of connecting the dots. > P.S. > changing a format of a whole result-set is not a solution for a > field-type-dependent problem. Since we're still arguing about whether it's a field-type-dependent problem or a field-use-dependent problem, I can't really argue this point since it assumes the former and I don't believe that's been shown yet. (*) Yes, it might be nice to have something that did it for you. Having one is not, in my mind, a requirement for the API but instead something to make it easier. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] have you feel anything when you read this ?
On Thu, 6 Apr 2006, Stephan Szabo wrote: > On Thu, 6 Apr 2006, Eugene E. wrote: > > If you meant that you must retrieve them in a separate query, you're > incorrect, since you *could* use the binary form for the others. I can't > understand if you don't realize that there is one for all these various > types, or that you just don't wish to use it (for example, I believe using > %d on ntohl(value from pqgetvalue) or something similar will print your > integer). For notes sake, the example code with binary retrieval in the docs seems to have ntohl(*((uint32_t *) iptr)) for iptr being the result of PQgetvalue on an integer column. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] have you feel anything when you read this ?
Stephan Szabo wrote: What would you expect it to do given a single result format argument? If you want to propose a new function (set of functions) that have different behavior, make a coherent proposal. > Statements like it should > do X because I want it to aren't coherent proposals. AFAIK, they convert each value before put it to a result set. I propose to do the following convertion to the textual-form for bytea values: X->X where X is byte [0..255] Okay, now pass that to strcmp or a %s format. AFAIK, the "textual-form" of values is meant to be a c-string. "ab\0cd\0" is not a c-string containing ab\0cd, it's a c-string containing ab. WHY strcmp ?! do you really think the user is a fool ? if the user declared something "binary", he obviously knows what he has done. WHY c-string ? the user only wants to get PGresult structure. Since this structure provides a length of each value, you have no need in c-string. Why do think the user needs it ? "textual-form" is just a name of actually existent convertion rule. i am not trying to find out a philosophy here. I think I don't exactly agree with this description, but I'm unclear exactly what you're saying. Are you saying that textual-form is the useful representation, or are you saying that textual-form is the representation and it is useful? the actual representasion of most types is pretty useful. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] have you feel anything when you read this ?
Hi, Stephan & Eugene, Stephan Szabo wrote: > This is already false AFAICS. Leading or trailing spaces on a string > containing integer get trimmed during the input for example, the string > format of date comes back in a particular but other input formats are > supported. I don't think the above equality is valid for textual > representation. It is even true that the server-internal storage format can be distinct from both the textual and binary representation (aka canonical rep.). This is e. G. how PostGIS handles their geometries. PostGIS geometries have even more representations, available via conversion functions. And for some unicode strings, it even happens that their textual representation is different depending on the client encoding. >>I expect a value of BYTEA to be unchaged too. > I think (as above) that your perception of the problem isn't correct. I agree. The value of the BYTEA is unchanged, it is just a different representation of the BYTEA that allows handling its contents as text, in non-binary safe environments. Imagine having the text representation as simply HEXing the BYTEA contents - it still is an unchanged value. >>why not to define your own unique more_sofisticated representation ? >>(as for bytea is defined.) > AFAICS, there is one, the binary format for integer. Exactly. AFAICS, all built-in data types have both a text and binary representation, as well as most extension types. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] have you feel anything when you read this ?
On Thu, 6 Apr 2006, Eugene E. wrote: > Stephan Szabo wrote: > >>>What would you expect it to do given a single result format argument? > >>> > >>>If you want to propose a new function (set of functions) that have > >>>different behavior, make a coherent proposal. > >> > >> > Statements like it should > >> > do X because I want it to aren't coherent proposals. > >> > >>AFAIK, they convert each value before put it to a result set. > >>I propose to do the following convertion to the textual-form for bytea > >>values: > >>X->X where X is byte [0..255] > > > > > > Okay, now pass that to strcmp or a %s format. AFAIK, the "textual-form" of > > values is meant to be a c-string. "ab\0cd\0" is not a c-string containing > > ab\0cd, it's a c-string containing ab. > > WHY strcmp ?! do you really think the user is a fool ? > if the user declared something "binary", he obviously knows what he has > done. > > WHY c-string ? the user only wants to get PGresult structure. > Since this structure provides a length of each value, you have no need > in c-string. Why do think the user needs it ? > > "textual-form" is just a name of actually existent convertion rule. > i am not trying to find out a philosophy here. Then, honestly, nothing anyone can say will help, because you're not willing to actually hold a conversation on the topic. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECT composite type
On Wednesday 05 April 2006 19:29, Tom Lane wrote: > Niklas Johansson <[EMAIL PROTECTED]> writes: > > You could try > > > > SELECT foo.x, foo.y, title FROM > > (SELECT > >get_xy(SetSRID(sightings.location, 26910), 4326) AS foo, > >sightings.title > > FROM sightings > > WHERE sighting_id = 25) bar; > > Note however that the above is only a cosmetic answer: you avoid typing > the function call twice, but the planner will "flatten" the subquery > into the outer query and thereby end up with two evaluations anyway. > If you're really intent on avoiding the extra evaluation then you need > to do something to prevent the flattening from happening. One > handy trick is to use a LIMIT or OFFSET clause in the subquery as an > optimization fence: Thanks for the suggestions. This case is mostly for aesthetic reasons, or perhaps to prevent typing errors in the future (in case I have to modify the function call). Forming the sub-query that way will effectively solve my problem, even if it doesn't specifically aid in efficiency -- however my get_xy function is STABLE, so it shouldn't harm it too much (unsure about SetSRID); additionally, adding the OFFSET 0 is an interesting trick that I will also try. Thus, my final query: SELECT (xy).x, (xy).y, title FROM (SELECT get_xy(SetSRID(sightings.location, 26910), 4326) AS xy, sightings.title FROM sightings WHERE sighting_id = 25 OFFSET 0) bar; Thanks again. Mike. -- Michael Burke Engineering Technologies Canada Ltd. - http://www.engtech.ca/ [EMAIL PROTECTED] 1 (902) 628-1705 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] have you feel anything when you read this ?
Hi, Eugene, Eugene E. wrote: >> Okay, now pass that to strcmp or a %s format. AFAIK, the >> "textual-form" of >> values is meant to be a c-string. "ab\0cd\0" is not a c-string containing >> ab\0cd, it's a c-string containing ab. > WHY strcmp ?! do you really think the user is a fool ? > if the user declared something "binary", he obviously knows what he has > done. But when the user requests the canonical _text_ representation of a byte area data type, why do you consider him declaring it "binary"? > WHY c-string ? the user only wants to get PGresult structure. And he does request the _text_ represenation of the datatypes in this structure. > Since this structure provides a length of each value, you have no need > in c-string. Why do think the user needs it ? A user that does not have a need in C-Strings can fetch the binary representation, getting higher efficency for all datatypes. > "textual-form" is just a name of actually existent convertion rule. > i am not trying to find out a philosophy here. There is no philosophy but orthogonality. There's a textual and a binary form of datatypes. For varchar, byta, int4, float, PostGIS geometries etc... >> I think I don't exactly agree with this description, but I'm unclear >> exactly what you're saying. Are you saying that textual-form is the >> useful representation, or are you saying that textual-form is the >> representation and it is useful? > the actual representasion of most types is pretty useful. The text representation is pretty useful for human readers for _most_ datatypes, the binary representation is much easier to parse for programs. So use the binary representation for everything if you don't want to display the data to the user directly. One could speculate that the textual representation is just a little help for "generic" tools like pg_dump, pgadmin or psql that display data to the user without having any knowledge of the specific datatypes (and without the possibility to have such knowledge). Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] have you feel anything when you read this ?
Markus Schaber wrote: Hi, Eugene, Eugene E. wrote: Okay, now pass that to strcmp or a %s format. AFAIK, the "textual-form" of values is meant to be a c-string. "ab\0cd\0" is not a c-string containing ab\0cd, it's a c-string containing ab. WHY strcmp ?! do you really think the user is a fool ? if the user declared something "binary", he obviously knows what he has done. But when the user requests the canonical _text_ representation of a byte area data type, why do you consider him declaring it "binary"? he did not request this representation. it is _by_default_ if you wish to provide it by request, please do it. i ask you to provide minimal convertion by default, mentioned the user wants his data unchanged. and let the user interpret his own data himself. Since this structure provides a length of each value, you have no need in c-string. Why do think the user needs it ? A user that does not have a need in C-Strings can fetch the binary representation, getting higher efficency for all datatypes. and lose the pretty good representation of all other columns in the same request. "textual-form" is just a name of actually existent convertion rule. i am not trying to find out a philosophy here. There is no philosophy but orthogonality. There's a textual and a binary form of datatypes. For varchar, byta, int4, float, PostGIS geometries etc... good. i ask you to slightly change "textual" representation of bytea. The text representation is pretty useful for human readers for _most_ datatypes, the binary representation is much easier to parse for programs. You are right. but Who said that i can not display something ? i thougth, human-readability of some data depends completely on how CLIENT-SIDE interpret it. server do not know and should not know what data is human readable or printable... etc. So use the binary representation for everything if you don't want to display the data to the user directly. The problem we discuss is not about displaing or printig at all. Some applications want "textual-form" -- most applications but not only to display and in the _same_ query the same applications want bytea... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pgcrypto-crypt
On Thu, 2006-06-04 at 13:53 +0530, AKHILESH GUPTA wrote:
> dear all,
> i want to encrypt and decrypt one of the fields in my table (i.e-
> password field)
> i have searched and with the help of pgcrypto package, using function
> "crypt", i am able to encrypt my data,
> but there is nothing which i found to decrypt that same data,
> plz anybody give me the function to decrypt that encrypted value.
> plz reply asap
I found this with Google, maybe it will help you.
CREATE TABLE crypto (
id SERIAL PRIMARY KEY,
title VARCHAR(50),
crypted_content BYTEA
);
INSERT INTO crypto VALUES (1,'test1',encrypt('daniel', 'fooz', 'aes'));
INSERT INTO crypto VALUES (2,'test2',encrypt('struck', 'fooz', 'aes'));
INSERT INTO crypto VALUES (3,'test3',encrypt('konz', 'fooz', 'aes'));
SELECT * FROM crypto;
SELECT *,decrypt(crypted_content, 'fooz', 'aes') FROM crypto;
SELECT *,decrypt(crypted_content, 'fooz', 'aes') FROM crypto WHERE
decrypt(crypted_content, 'fooz', 'aes') = 'struck';
I could not test it, since I do not have pgcrypto installed.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] pgcrypto-crypt
Guy Fraser wrote:
> On Thu, 2006-06-04 at 13:53 +0530, AKHILESH GUPTA wrote:
> > dear all,
> > i want to encrypt and decrypt one of the fields in my table (i.e-
> > password field)
> > i have searched and with the help of pgcrypto package, using function
> > "crypt", i am able to encrypt my data,
> > but there is nothing which i found to decrypt that same data,
The 'crypt' function in pgcrypto is analogous to the unix crypt(3) function,
which is actually a hashing function and not an encryption function -- meaning
you *can't* (realistically) decrypt it. Use 'encrypt', as demonstrated below.
> INSERT INTO crypto VALUES (1,'test1',encrypt('daniel', 'fooz', 'aes'));
> INSERT INTO crypto VALUES (2,'test2',encrypt('struck', 'fooz', 'aes'));
> INSERT INTO crypto VALUES (3,'test3',encrypt('konz', 'fooz', 'aes'));
>
> SELECT * FROM crypto;
>
> SELECT *,decrypt(crypted_content, 'fooz', 'aes') FROM crypto;
>
> SELECT *,decrypt(crypted_content, 'fooz', 'aes') FROM crypto WHERE
> decrypt(crypted_content, 'fooz', 'aes') = 'struck';
>
> I could not test it, since I do not have pgcrypto installed.
This works perfectly.
---(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] Query from shell
Hi every body, somebody can show me hot to execute a query from a shell thanks in advanced!!! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Query from shell
Judith wrote: >Hi every body, somebody can show me hot to execute a > query from a shell echo QUERY HERE | psql databasename Or, if you want to run several queries, run psql and run your queries there. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Query from shell
Owen Jacobson wrote: Judith wrote: Hi every body, somebody can show me hot to execute a query from a shell echo QUERY HERE | psql databasename Or, if you want to run several queries, run psql and run your queries there. or psql -d -c "your query here" ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Query from shell
On Thursday 06 April 2006 02:37 pm, Judith saith: >Hi every body, somebody can show me hot to execute a query from a > shell > > thanks in advanced!!! > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org Do this: psql -c 'SELECT code FROM cust' rnd; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Query from shell
On Thursday 06 April 2006 15:37, Owen Jacobson wrote: > Judith wrote: > >Hi every body, somebody can show me hot to execute a > > query from a shell > > echo QUERY HERE | psql databasename > > Or, if you want to run several queries, run psql and run your queries > there. > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster I think you can also run: psql -f filename.sql database_name ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Query from shell
or psql db < To: Sent: Thu, 6 Apr 2006 14:37:51 -0700 Subject: Re: [SQL] Query from shell > Judith wrote: > > >Hi every body, somebody can show me hot to execute a > > query from a shell > > echo QUERY HERE | psql databasename > > Or, if you want to run several queries, run psql and run your queries there. > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster --- End of Original Message --- ---(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
