[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
Re: [SQL] Encoding and result string length
On Thursday 08 April 2004 10:32, kumar wrote:
>
> 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;
> So i tried
> select encode('c1','base64') from encodeco;
Not quite:
SELECT encode(c1::text, 'base64') FROM encodeco;
or
SELECT encode(CAST(c1 AS text), 'base64') FROM encodeco;
> 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
You could write such a coding so long as you don't mind having ten-thousand
characters in your character-set. Of course, character does not equal byte
like this, but that's true in the various unicode systems anyway.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] SQL challenge--top 10 for each key value?
Offered up for anyone with time on their hands. I fiddled around with
this for half an afternoon, then gave up and did it programmatically in
Perl.
Given a table that looks something like this:
id | INTEGER
query| INTEGER
checksum | char(32)
score| INTEGER
include | BOOLEAN
The table is unique by "id". "Checksum" may be repeated, but I only care
if it is repeated within a given group by "query". ("query" is non-null.)
I can get the top scorer for each "query" row by something like this:
SELECT * FROM (
SELECT DISTINCT ON (checksum) *
FROM my_table
ORDER BY checksum, score DESC)
ORDER BY query;
How would you go about getting the top N (say, the top 10) for each query?
And then, if that's too easy for you--consider a further case where I
want every row for a given "query" that has "include" TRUE, and enough
non-"include" rows to make N. I might end up with more than N rows for a
given value of "query" if there were more than N with "include" set.
I headed off in the direction of groups of SELECTs and UNIONs, and quit
when I got to something like four levels of "SELECT ... AS FOO" ...
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL challenge--top 10 for each key value?
Jeff Boes <[EMAIL PROTECTED]> writes:
> I headed off in the direction of groups of SELECTs and UNIONs, and quit when I
> got to something like four levels of "SELECT ... AS FOO" ...
four? wimp, that's nothing!
ok, seriously I think there's no way to do this directly with straight SQL.
You would have to define a non-immutable function that has some temporary
storage where it keeps track of how many it has seen.
The generic function that would help here would be some kind of rank(value)
that would give you the equivalent of rownum except with a level break every
time value changes. I've been hoping to see something like this on the list
for a long time but haven't yet.
If the value of n is constant and small you could cheat with an aggregate
function with an array of the top n values.
db=> create function first3_accum(integer[],integer) returns integer[] as 'select case
when array_upper($1,1) >= 3 then $1 else array_append($1,$2) end' language sql strict
immutable;
CREATE FUNCTION
db=> create aggregate first3 (basetype = integer, sfunc = first3_accum, stype =
integer[], initcond = '{}');
CREATE AGGREGATE
then something like:
SELECT first3(id)
FROM (SELECT id
FROM my_table
ORDER BY query,
CASE WHEN include THEN 1 ELSE 2 END ASC,
score DESC)
GROUP BY query
But then you'll have to go back to the table to refetch the original records
that you've found. The best way I find to do that is with the int_array_enum()
function from the int_aggregate contrib module.
SELECT *
FROM my_table
WHERE id IN (
SELECT int_array_enum(f3)
FROM (
SELECT first3(id) as f3
FROM (SELECT id
FROM my_table
ORDER BY query,
CASE WHEN include THEN 1 ELSE 2 END ASC,
score DESC) as x
GROUP BY query
) as x
)
This last step is kind of annoying since you've already seen all those
records. And it requires writing a new aggregate function every time the value
of n changes though, which kind of sucks.
In theory if the new work in 7.5 handling structured datatypes is as cool as
it sounds you could have an array of complete records and when UNNEST is
eventually incorporated into the array code then you could expand those
instead of using the int_array_enum function. Neither of those things are
ready yet as far as I know though.
--
greg
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SQL challenge--top 10 for each key value?
On Thu, 2004-04-08 at 19:33, Greg Stark wrote: > Jeff Boes <[EMAIL PROTECTED]> writes: > > > I headed off in the direction of groups of SELECTs and UNIONs, and quit when I > > got to something like four levels of "SELECT ... AS FOO" ... > > four? wimp, that's nothing! > > ok, seriously I think there's no way to do this directly with straight SQL. > You would have to define a non-immutable function that has some temporary > storage where it keeps track of how many it has seen. I don't believe that is true, though it is certainly is in PostgreSQL. The spec has the ability to apply a progressive aggregate on the results of a query (window function). Meaning you can accomplish things like counting (ROW_NUMBER) or running totals. Something along the lines of the below would accomplish what you want according to spec. ROW_NUMBER() is a spec defined function. (6.10 of SQL200N) SELECT * FROM (SELECT ROW_NUMBER() OVER (DISTINCT query) AS counter ) WHERE counter > 10; ---(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] SQL challenge--top 10 for each key value?
Rod Taylor <[EMAIL PROTECTED]> writes: > ROW_NUMBER() is a spec defined function. (6.10 of SQL200N) If the spec doesn't even have a year number yet, you can hardly expect real implementations to support it ;-). There is no such thing in the extant specs SQL92 or SQL99. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
