[SQL] Inheriting text[] field
Dear Friend, I have the following problem when I try to inherits one table with text[] field into another. I am useing PostgreSQL 7.2.3. I suppose that this is a BUG but I am not sure. Any ides. 10x in advance. Kaloyan test_libvar=# create table temp_a( test_libvar(# name text[] test_libvar(# ); CREATE test_libvar=# create table temp( name text[] ) inherits (temp_a); NOTICE: CREATE TABLE: merging attribute "name" with inherited definition ERROR: CREATE TABLE: attribute "name" type conflict (_text and text) P.S. I know that I can avoid this by scipping the 'name' field into the second table but what if I need to set some CONSTRAINTS to it. ---(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] duplicate table in two databases
Fabio Ferrero wrote: I use postgres 7.2.1 (debian woody) and PHP. I've two databases (foo and bar for example) with the same table (ie. stamps). It's possible with a single query transfer the data from the stamps table in db foo to the stamps table in db bar? You might want to look in the contrib/ folder of the source distribution (or the equivalent in your packaged installation for "dblink". Haven't used it myself, but it can certainly do what you want here. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CROSS-TAB query help? I have read it cant be done in one
Theo Galanakis wrote: Does anyone know how to perform a cross-tab query in ONE SQL without having to write a SP? The SQL at the end of this email attempts to display the subquery result-set in a cross-tab format, it does not group the content onto one row as it should in the sample below. SQL is below if it makes any sense, however the sub-query returns data as below. Examle: NameValue ID 1 Cola10 Colb20 Colc30 Cold40 Cole50 I want to output as: ID, cola, colb, colb, cold, cole 1 10 30 30 40 50 Actual Output: content_object_id | xpos | ypos | text| textangle | texttype | symbol | linktype ---+--+--+---+---+-- 100473 | 93 | | | | 100473 | | 77 | | | 100473 | | | text1| | Don't forget the provided crosstab functions (in contrib/). If you don't want that, you could aggregate your results: SELECT content_object_id, MAX(xpos), MAX(ypos), ... FROM ( ) AS raw GROUP BY content_object_id; -- Richard Huxton Archonet Ltd ---(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] Returning A Varchar From A Function
Hi Richard,
What happens if you just do:
where trim(status) = trim($1)
Regards,
George
- Original Message -
From: "Richard Hurst" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, August 12, 2004 4:04 AM
Subject: [SQL] Returning A Varchar From A Function
> Hi
>
> this has been puzzling me for a few days now
>
> I have created a function that I want to use in a database to select a
> value from a table based on the value passed in.
> The table is fairly simple
> CREATE TABLE public.feeder_next_status
> (
> status varchar NOT NULL,
> previous_status varchar,
> next_status varchar
> ) WITH OIDS;
>
> The function is defined as
>
> -- Function: public.spgetnextstatus(varchar)
>
> -- DROP FUNCTION public.spgetnextstatus(varchar);
>
> CREATE OR REPLACE FUNCTION public.spgetnextstatus(varchar)
> RETURNS varchar AS
> '
> select cast(next_status as varchar)
> from feeder_next_status
> where trim(status) = trim(\'$1\')
> order by next_status;'
> LANGUAGE 'sql' STABLE;
>
>
> However when i run the query
> select spgetnextstatus('NEW')
> in pgadmin
> the dataoutput shows two columns
> the row column shows a row number of '1' and the column header
> spgetnextstatus(varchar) shows blank
>
> I have tested the equivalent sql in the pgadmin query and it works
> fine.
>
> Hoping someone can point me inthe right direction
>
>
>
> ---(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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Inheriting text[] field
Kaloyan Iliev Iliev <[EMAIL PROTECTED]> writes: > I am useing PostgreSQL 7.2.3. > test_libvar=# create table temp_a( > test_libvar(# name text[] > test_libvar(# ); > CREATE > test_libvar=# create table temp( name text[] ) inherits (temp_a); > NOTICE: CREATE TABLE: merging attribute "name" with inherited definition > ERROR: CREATE TABLE: attribute "name" type conflict (_text and text) Works fine for me in 7.3 and later. Time to upgrade ... regards, tom lane ---(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] SQL_ASCII and UNICODE server_encoding
Hi, i'd like to ask if there is any difference in server_encoding SQL_ASCII and UNICODE (there must be one since the different terms), but AFAIU -databases which store in SQL_ASCII (i.e. server_encoding=SQL_ASCII) have no problem storing 8bit chars with the first bit set. -databases with server_encoding=UNICODE actually store in UTF-8 encoding -The purpose of UTF-8 is to make possible that multibyte chars can be stored in systems that represent chars in 8 bits -So SQL_ASCII is fine for UTF-8, hence multibyte UNICODE. So if SQL_ASCII should be fine for UTF-8 storage. (here i must have missed something, tho..), whats the purpose of server_encoding=UNICODE? -- -Achilleus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL_ASCII and UNICODE server_encoding
Achilleus Mantzios <[EMAIL PROTECTED]> writes: > So if SQL_ASCII should be fine for UTF-8 storage. > (here i must have missed something, tho..), whats > the purpose of server_encoding=UNICODE? If you use SQL_ASCII, the server will *store* Unicode just fine, but it won't *know* it is Unicode. So if you just want raw data storage it doesn't matter. If you would like to sort the data, upper-case or lower-case it, or have automatic conversions to different client encodings, you had better tell the server the truth about what it is storing. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL_ASCII and UNICODE server_encoding
O kyrios Tom Lane egrapse stis Aug 16, 2004 : > Achilleus Mantzios <[EMAIL PROTECTED]> writes: > > So if SQL_ASCII should be fine for UTF-8 storage. > > (here i must have missed something, tho..), whats > > the purpose of server_encoding=UNICODE? > > If you use SQL_ASCII, the server will *store* Unicode just fine, but > it won't *know* it is Unicode. So if you just want raw data storage > it doesn't matter. If you would like to sort the data, upper-case > or lower-case it, or have automatic conversions to different client > encodings, you had better tell the server the truth about what it > is storing. Thanx, i guess i didnt realize the value of conversion,sorting,etc... > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] any chance SQL ASSERTIONS will be implemented?
Hi, in the course of designing a database schema and ensuring integrity everywhere I have stumbled over a problem that Josh told me ASSERTIONS would solve. Is there any chance ASSERTIONS will be implemented in PostgreSQL? Thanks -- Markus Bertheau <[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
Re: [SQL] any chance SQL ASSERTIONS will be implemented?
Markus Bertheau wrote: > in the course of designing a database schema and ensuring integrity > everywhere I have stumbled over a problem that Josh told me > ASSERTIONS would solve. Is there any chance ASSERTIONS will be > implemented in PostgreSQL? There is a pretty good chance, but there is no telling when it will happen. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Verifying data type
Hello guys, I have a function like this CREATE OR REPLACE FUNCTION "public"."f_tipo_campo" (varchar) RETURNS numeric AS 'declare p_valor ALIAS FOR $1; v_resultado varchar; begin v_resultado := 1; <> return v_resultado; <>end; <><>' LANGUAGE 'plpgsql' <> <> I need to verify if the data in p_valor is just number or it is a string. What is the best way to do this? Here is the original function in Oracle PL/SQL create or replace function f_tipo_campo (p_valor varchar) return number is v_resultado number; v_numbernumber; begin begin select p_valor into v_number from dual; v_resultado := 0; exception when others then v_resultado := 1; end; return v_resultado; end; / show err -- Elieser Leão Sorry for my bad english... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Verifying data type
=?ISO-8859-1?Q?Elieser_Le=E3o?= <[EMAIL PROTECTED]> writes: > I need to verify if the data in p_valor is just number or it is a string. > What is the best way to do this? In PG 8.0 you can just do it exactly the way your Oracle original does, viz try to cram it into a numeric variable and catch the exception if any. In earlier versions, my thoughts would run to some kind of string matching test using a regular expression. The regexp method is probably significantly faster though, so maybe you want to do it anyway, especially if you don't need the full generality of possible floating-point formats. You might get away with something as simple as "p_valor ~ '^[0-9]+$'" if you only care about unsigned integer inputs. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] CROSS-TAB query help? I have read it cant be done in on
Title: RE: [SQL] CROSS-TAB query help? I have read it cant be done in one SQL, pro ve them wrong! Thanks Rickard Max may not work as not all the data is numerical. However I will give the contrib/cross-tab a go! Theo -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED]] Sent: Monday, 16 August 2004 6:06 PM To: Theo Galanakis Cc: '[EMAIL PROTECTED]' Subject: Re: [SQL] CROSS-TAB query help? I have read it cant be done in one SQL, pro ve them wrong! Theo Galanakis wrote: > Does anyone know how to perform a cross-tab query in ONE SQL without > having to write a SP? The SQL at the end of this email attempts to > display the subquery result-set in a cross-tab format, it does not > group the content onto one row as it should in the sample below. SQL > is below if it makes any sense, however the sub-query returns data as > below. > > Examle: > > Name Value > ID 1 > Cola 10 > Colb 20 > Colc 30 > Cold 40 > Cole 50 > > I want to output as: > > ID, cola, colb, colb, cold, cole > 1 10 30 30 40 50 > Actual Output: > > content_object_id | xpos | ypos | text | textangle | texttype > | symbol | linktype > ---+--+--+---+---+ > ---+--+--+---+---+ > ---+--+--+---+---+-- > 100473 | 93 | | | | > 100473 | | 77 | | | > 100473 | | | text1 | | Don't forget the provided crosstab functions (in contrib/). If you don't want that, you could aggregate your results: SELECT content_object_id, MAX(xpos), MAX(ypos), ... FROM ( ) AS raw GROUP BY content_object_id; -- Richard Huxton Archonet Ltd __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
