Re: [SQL] Convert int to hex
Hi, You can use to_hex() function to convert integer to hexa number. I have tried with a small example. create table testing( id int4 , sHex varchar( 20 ) ) insert into testing( id ) values( 204678 ); insert into testing( id ) values( 2076876 ); update testing set sHex = ( cast( to_hex( id ) as varchar( 20 ) ) ) select * from testing Id | shex ---|-- 204678 | 31f86 2076876| 1fb0cc Regards, R.Muralidharan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Fernando Grijalba Sent: Thursday, June 02, 2005 2:12 AM To: pgsql-sql@postgresql.org Subject: [SQL] Convert int to hex I want to be able to change an int4 from a sequence and store it as varchar in the database as a hex number. Is this possible? Thank you, Fernando ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Returning a Cross Tab record set from a function
I have read the great work that all the list members have done working
with cross tabs (pivot tables) in postgresql. The issue I have not seen
a solution for, but would really like to see, is the ability to return
the results of a dynamic (variable # of columns) cross tab function as a
recordset.
The excellent code contributed by Christoph Haller in the "Generating a
cross tab II (pivot table)" thread was very useful, but it dumps the
results into a view. I need to query like "select * from
create_pivot_report('sales_report2','vendor','product','sales','sum','sales');"
and have the result back as a recordset.
The reason I want to do this is that I have a hierarchical structure of
itemtypes where each itemtype contains an arbitrary number of items, AND
each itemtype has an arbitrary number of attributes. I want to perform
the crosstab on the items with attributes for a given itemtype. The
static code works perfectly fine for a query of an itemtype, BUT the
itemtypes and attributes may change often enough that creating views for
each itemtype will be insufficient.
It seems I can do this from any higher level language, but it drives me
crazy that I can't perform this operation as a function inside of
Postgres...
Thanks for any thoughts you might have...
--
Marc Wrubleski
---(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
[SQL] Unique keys on views
Hi Is it possible to refer to a unique row identifier on a view ? I have the following view but in a subsequent select I need to refer to each row's unique identifier and I know oid's are not valid for a view. create view persontransit as select personid, planet, name as aspectname, position as planetposition, position+angle as transitposition from personplanet, aspect union select personid, planet, name as aspectname, position as planetposition, position-angle as transitposition from personplanet, aspect where name != 'OPPOSITION'; Many thanks David ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Multiple SRF parameters from query
Hi,
I am not able to understand "Returning only one row", since 'aaa' having 2
rows and 'bbb' having 3 rows and what criteria single row should be returned.
Please let me know the expected result and I will try to find out a solution
for it.
for set returning function the following link maybe helpful
http://techdocs.postgresql.org/guides/SetReturningFunctions
Regards,
R.Muralidharan
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Federico Pedemonte
Sent: Tuesday, May 31, 2005 8:23 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Multiple SRF parameters from query
Hi All,
i'm having troubles trying to write a sql query using a Set Returning
Function.
I have a function foo (ID varchar) that returns a variable number of
records.
I have also a table (called Anagrafica) that contains a list of IDs to
be passed to the function foo.
As an example, just say that Anagrafica is like this:
Anagrafica
ID | n
+---
aaa | 1
bbb | 5
ccc | 9
ddd | 10
eee | 11
and foo returns this values
select * from foo ('aaa')
a | b | c
--+---+---
1 | 2 | 3
5 | 9 | 1
select * from foo ('bbb')
a | b | c
--+---+---
4 | 0 | 0
2 | 0 | 0
0 | 0 | 0
what i would like to do is write an SQL query that looks something like
this (written in simplyfied-pseudo-sql)
SELECT FROM anagrafica
WHERE n < 5
SELECT * FROM Foo (anagrafica.ID)
and that gives as result a table that is the union of foo ('aaa') and
foo ('bbb')
a | b | c
--+---+---
1 | 2 | 3
5 | 9 | 1
4 | 0 | 0
2 | 0 | 0
0 | 0 | 0
I'm having success if foo returns only a row, but the problem is that
foo returns more than one.
I think i sould use a join but i couldn't find the right syntax for it.
I don't want to create a spefic PL/pgSQL function becase i need to have
freedom in selecting IDs from Anagrafica (the one i provided is just an
example, the real table is much more complex).
I would really appreciate any suggestion.
Best regards,
Federico.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(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
[SQL] plpgsql and triggers
I'm looking for a way to use a parameter given to a trigger function as fieldname. It should be something like
create function f_makeupper() returns trigger as '
begin
NEW.TG_ARGV[0] := upper(NEW.TG_ARGV[0]);
RETURN NEW;
end;
' language 'plpgsql';
create trigger "TRIG_tbltest" before insert on tbltest for each row execute procedure f_makeupper("fieldname");
I can create both the function and the trigger, but on inserting a new record I get an error telling me that "record 'new' has no field 'tg_argv'"
What am I doing wrong ?
[SQL] How do write a query...
Hello, I have the following problem : I have a table like IdNum Date AValue 1 10 01/01/2005 50 2 10 31/05/2005 60 3 25 02/02/2005 55 4 25 15/03/2005 43 5 25 28/05/2005 62 etc.. Id is unique, Num is an identification number with duplicates possible, date is a ... date and Avalue... a value! If we have IdNum Date AValue Id1 Num1Date1 AValue1 Id2 Num1Date2 AValue2 The table is ordered on Num+Date. What I would like to calculate is (AValue2-AValue1) for a given Num (here num1). In this case, I would have to calculate 60-50 for Num 10 and 43-55, 62-43 for Num 25. Do you have any idea if it can be done simply with a request... I thank you Regards. Alain Reymond ---(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] index row size 2728 exceeds btree maximum, 2713
Hi It is not advisable to add a variable length data field in the Index key, since it is very difficult predict the size of the field which may vary from record to record. are you included this field for Full text search on data field? Regards, R.Muralidharan -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Dinesh PandeySent: Thursday, January 01, 2004 3:14 PMTo: Ramakrishnan Muralidharan; pgsql-general@postgresql.org; 'PostgreSQL'Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Hi, One of the columns in primary key is of type “TEXT”. I am able to insert with small data, but for around 3000 characters it’s failing. How to handle that? ThanksDinesh Pandey From: Ramakrishnan Muralidharan [mailto:[EMAIL PROTECTED] Sent: Thursday, June 02, 2005 3:11 PMTo: [EMAIL PROTECTED]; pgsql-general@postgresql.org; PostgreSQLSubject: RE: [SQL] index row size 2728 exceeds btree maximum, 2713 Hi, The issue looks like your Index width exceeds the maximum width of the index key limit, Please review the keys used in the index. Regards, R.Muralidharan -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Dinesh PandeySent: Thursday, June 02, 2005 12:35 PMTo: pgsql-general@postgresql.org; 'PostgreSQL'Subject: [SQL] index row size 2728 exceeds btree maximum, 2713 TABLE ---+---+--- Column | Type ---+---+--- scan_id | bigint host_ip | character varying(15) port_num | integer plugin_id | integer severity | character varying(50) data | text Indexes: "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, data) On inserting record I am getting this error “index row size 2728 exceeds btree maximum, 2713” How to solve this problem?
Re: [GENERAL] [SQL] index row size 2728 exceeds btree maximum, 2713
On Thu, Jan 01, 2004 at 03:13:48PM +0530, Dinesh Pandey wrote: > One of the columns in primary key is of type "TEXT". I am able to insert > with small data, but for around 3000 characters it's failing. How to handle > that? Easy, btree indexes can't handle data with more that 2713 bytes. You need to decide if having a single index on all your columns is actually what you want. Depending on your queries it may not even be used. Hope this helps, > From: Ramakrishnan Muralidharan > [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 02, 2005 3:11 PM > To: [EMAIL PROTECTED]; pgsql-general@postgresql.org; PostgreSQL > Subject: RE: [SQL] index row size 2728 exceeds btree maximum, 2713 > > > > Hi, > > > > The issue looks like your Index width exceeds the maximum width > of the index key limit, Please review the keys used in the index. > > > > Regards, > > R.Muralidharan > > > > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Dinesh Pandey > Sent: Thursday, June 02, 2005 12:35 PM > To: pgsql-general@postgresql.org; 'PostgreSQL' > Subject: [SQL] index row size 2728 exceeds btree maximum, 2713 > > TABLE > > ---+---+--- > > Column| Type > > ---+---+--- > > scan_id| bigint > > host_ip| character varying(15) > > port_num | integer > > plugin_id | integer > > severity | character varying(50) > > data | text > > Indexes: > > "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, port_num, > plugin_id, severity, data) > > > > > > On inserting record I am getting this error "index row size 2728 exceeds > btree maximum, 2713" > > > > How to solve this problem? > > > > > > > -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpA5YgJkewCM.pgp Description: PGP signature
Re: [SQL] index row size 2728 exceeds btree maximum, 2713
Hi, The issue looks like your Index width exceeds the maximum width of the index key limit, Please review the keys used in the index. Regards, R.Muralidharan -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Dinesh PandeySent: Thursday, June 02, 2005 12:35 PMTo: pgsql-general@postgresql.org; 'PostgreSQL'Subject: [SQL] index row size 2728 exceeds btree maximum, 2713 TABLE ---+---+--- Column | Type ---+---+--- scan_id | bigint host_ip | character varying(15) port_num | integer plugin_id | integer severity | character varying(50) data | text Indexes: "pk_scanned_port_info" PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, data) On inserting record I am getting this error “index row size 2728 exceeds btree maximum, 2713” How to solve this problem?
Re: [SQL] plpgsql and triggers
On Thu, Jun 02, 2005 at 01:57:26PM +0200, Bart Degryse wrote: > > I'm looking for a way to use a parameter given to a trigger function as > fieldname. It should be something like > create function f_makeupper() returns trigger as ' > begin > NEW.TG_ARGV[0] := upper(NEW.TG_ARGV[0]); > RETURN NEW; > end; > ' language 'plpgsql'; As you've discovered, this doesn't work in PL/pgSQL: the above code references the new row's TG_ARGV column, which doesn't exist. This comes up frequently; see the list archives for past discussion. The usual advice is to use a language like PL/Perl, PL/Tcl, PL/Python, etc. that provides this capability. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
