Hi,
I am using Postgresql version 7.2.2
I made a small function...
CREATE FUNCTION ejem1(varchar) RETURNS SETOF to varchar as'
SELECT names from mi_tabla WHERE city = $1; '
language ' SQL ';
ejem1
Sergio
Carlos
Fernando
When wanting to obtain
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
Thank you, I found out after I posted the message.
I did read the docs but must have looked somewhere else and I search
the lists for hex only and it did not bring the answer back. It
wasn't until I search for int4 to hex that I was able to find the
answer.
Thank you again.
Fernando
On 6
Subject: Migracion de SQL Server 2000 a PostgreSQL
hola a todos, tengo un problema hace unos dias y necesito resolverlo cuanto antes. Necesito migrar la Base de Datos de un Portal Corporativo que desarrolle en SQL a Postgresql, pero no encuentro manera de hacerlo, trate de hacerlo por el export de
Hola a todo... necesito ejecutar una consulta Insert que realice en pgsql para ver si inserta correctamente en la tabla asociada, pero con execute me da un erroralguien me puede decir como lo hago
yo trabajo con el editor postgresql manager pro..
Gracias...
Ahh otra cosa: en otra cons
quiero darme de baja de la lista.mandenme el vinculo para hacerlo.
ect * from voipdb where prefix <= string order by prefix
> desc limit 1;
>
> Regards,
>
> -- Raju
Hum, I wonder if some kind of best-matching query is what you are looking
for:
SELECT *
FROM voipdb
WHERE prefix IN (
SELECT substr(string, 1, i)
FROM gene
ERE clause?
>
You could filter IP out with a regular expression:
select hostname as hosts_not_ip
from table
where hostname !~ '^[0-9]\.[0-9]\.[0-9]\.[0-9]$'
Cheers,
Fernando.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
>
> You could filter IP out with a regular expression:
>
> select hostname as hosts_not_ip
> from table
> where hostname !~ '^[0-9]\.[0-9]\.[0-9]\.[0-9]$'
>
Oops, i missed something. Its:
select hostname as hosts_not_ip
from table
where hostname !~ '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9
at kind of maintenance, and for my case 12
partitions are quite enough.
Any suggestions how to achieve this otherwise?
Regards,
Fernando.
>> B) SELECT * FROM some_table WHERE to_char(some_timestamp,
>> 'MMDD') >
>> to_char((now() - interval '1 day'), 'MMDD');
>
>I'd never use to_char to compare dates. The built-in comparison
>operators work just fine.
>
Why not? I'm curious if has anything to do with performance or just
ne) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))
(28 rows)
The plan shows that it scans the indexes for all partitions when it should
only scan indexes for partitions 4 and 5. Is my assumption correct? If it
is, could someone point me out what I am doing wrong? I can't figure out why
it doesn't work.
I think the caveats mentioned in the manual about constraint exclusion have
been taken into account here but I might have missed something.
Regards,
Fernando.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
I see. Thanks for the tip.
Regards,
Fernando.
-Mensaje original-
De: Tom Lane [mailto:[EMAIL PROTECTED]
Enviado el: Miércoles, 20 de Junio de 2007 19:37
Para: Fernando Hevia
CC: 'PostgreSQL SQL List'
Asunto: Re: [SQL] Constraint exclusion
"Fernando Hevia" <[E
> > I have a column with the following values (example below)
> >
> > 5673
> > 4731
> > 4462
> > 5422
> > 756
> > 3060
> >
> > I want the column to display the numbers as follows:
> >
> > 56.73
> > 47.31
> > 44.62
> > 54.22
> > 7.56
> > 30.60
> >
> > I have been playing around with string functions
return v_register.prefix;
end;
v_len := v_len - 1;
END LOOP;
raise 'Not found';
END;
$body$
LANGUAGE 'plpgsql' VOLATILE ;
ERROR: unrecognized exception condition "no_data"
SQL state: 42704
Context: compile of PL/pgSQL function "test" near li
>On Mon, Jun 25, 2007 at 04:20:37PM -0300, Fernando Hevia wrote:
>> when <> then return variable;
>> when <> then <> ;
>> when <> then <> ;
>
>Check out the FOUND variable in the documentation for the first two,
>and the "trap
a specific field of the returned row from outside the
function? How should I write the query in order to show only fields 1 and 3,
for example?
It's sad to bother with this syntax questions, but I've had a hard time
finding code examples online.
Regards,
Fernando.
--
>>"Fernando Hevia" <[EMAIL PROTECTED]> 2007-06-26 16:25 >>>
>>How do I refer a specific field of the returned row from outside the
>>function? How should I write the query in order to show only fields 1 and
3, for example?
>In case you would like
eated,'Mon-dd-') AS "Report Date",
(((EXTRACT (MONTH FROM pi.incident_date ))::integer - 1) / 3) + 1 AS
Quarter
...
Regards,
Fernando.
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
En nombre de Norm Garand
Enviado el: Martes, 17 de Julio de 2
ing the explain plan. It has ~31k rows.
Any hindsight will be greatly appreciated.
Regards,
Fernando.
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
En nombre de hubert depesz lubaczewski
Enviado el: Viernes, 10 de Agosto de 2007 05:00
Para: Kiran
CC: pgsql-sql@postgresql.o
De: hubert depesz lubaczewski [mailto:[EMAIL PROTECTED]
>>On Fri, Aug 10, 2007 at 04:40:34PM -0300, Fernando Hevia wrote:
>> Found your query is shorter and clearer, problem is I couldn't have it
use
>> an index. Thought it was a locale issue but adding a 2nd index with
Try this:
Select *
from view v1
where duration = (select max(duration) from view v2 where v2.phone_number =
v1.phone_number)
You could get more than one call listed for the same number if many calls
match max(duration) for that number.
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[E
uggestions using JOIN.
Cheers,
Fernando.
---(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
luate the function but I'm not sure what that means as it *is*
executing it every time.
My goal of course is that the function gets executed only once per row.
I'm using 8.2.4
Thanks for your hindsight.
Regards,
Fernando.
put. Thanks!
BTW, this explanation should go into the manual.
Regards,
Fernando.
---(end of broadcast)---
TIP 6: explain analyze is your friend
elp.
>
Hi Chuck,
Do you need those characters in your table? If not I think you will be
better off preprocessing the data before running copy.
Replacing those " for ' or directly removing them is quite simple if you are
working in Unix, actually it should be quite s
ld like to just remove single or double quotes you should do:
sed "s/[\'\"]//g" file_with_quotes.txt > file_without_quotes.txt
Say you want to replace quotes with a space, then:
sed "s/[\'\"]/ /g" file_with_quotes.txt > file_without_quotes.
if(c!=FILTER) putchar(c);
c=fgetc(fp);
}
exit (0);
}
/*--*/
compile as:
$ gcc -o test test.c
Execute as:
$ test test.txt
this is field1, and this is field2
Could this be of help?
Regards,
Fernando.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
onnectcause)::text =
'10'::text) AND (acctsessiontime > 0) AND ((calledstationid)::text !~
'^99[89]#'::text))"
" -> Bitmap Index Scan on idx_stopvoip_callingid2
(cost=0.00..29.29 rows=2939 width=0)"
"Index Cond: ((callingstationid)::text =
'2941605118'::text)"
Thanks,
Fernando.
essiontime > 0) AND ((calledstationid)::text !~ '^99[89]#'::text))
-> Bitmap Index Scan on idx_stopvoip_callingid2
(cost=0.00..29.29 rows=2939 width=0)
Index Cond: ((callingstationid)::text =
'2941605118'::text)
Thanks,
Fernando.
---(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
2007 so there shouldn't appear an empty
> knitting-column. --> classes.is_availlable
> 3) b) Or it is availlable but no one has chosen it in 2007. -->
> attends_to.in_year
>
Yes, you could. Read about different JOINS and WHERE clauses. ;)
Regards,
Fernando.
---(end of broadcast)---
TIP 6: explain analyze is your friend
-
2008-01-02 01:13:16.426-03
(1 row)
I'm lost here. ARST isn't new. It has been used in former years.
Any help would be greatly appreciated.
Regards,
Fernando
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
tom lane
Thanks Scott and Tom for your help on this.
After copying the updated tz file to /usr/share/postgresql/timezone Postgres
got aware of the time change.
Regarding the ARST recognition, I'm still on 8.1.9. :(
An upgrade seems urgent now.
Regards,
Fernando.
--
00:00 | t
America/Argentina/San_Juan | ARST | -02:00:00 | t
(11 rows)
Thanks for all contributions.
Regards,
Fernando.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
> Tom Lane [mailto:[EMAIL PROTECTED] wrote:
>
> "Fernando Hevia" <[EMAIL PROTECTED]> writes:
> > With 8.2.x the ARST abbreviation was recognized after including the
> > following line in /usr/share/postgresql/8.2/timezonesets/Default
>
> > AR
ET num = num + 1 WHERE num = v_num;
END LOOP;
INSERT INTO temp VALUES (p_num, p_name);
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
To run it:
sistema=# select insert_value(4, 'NOT');
insert_value
--
(1 row)
sistema=# select * from temp order
not understand this. Are you saying timestamps WITH timezone are NOT
indexable or you mean that you cant build a partial index on a
timestamp-with-time-zone returning function?
Regards,
Fernando.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscripti
> -Mensaje original-
> De: Scott Marlowe [mailto:[EMAIL PROTECTED]
>
>
> Note that if you are storing your time stamp as timestamptz,
> you can use the "at time zone 'xyz'" construct to create an
> index, and as long as you retrieve them with the same
> construct you'll get to use
> -Mensaje original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] En nombre de Stephan Szabo
> Enviado el: Viernes, 25 de Abril de 2008 17:46
> Para: Emi Lu
> CC: pgsql-sql@postgresql.org
> Asunto: Re: [SQL] trim(both) problem?
>
> On Fri, 25 Apr 2008, Emi Lu wrote:
>
> > Hi,
>
4 and I would like to convert the 14 weeks to 3
> months (some lose of accuracy expected).
> Expected tests may be:
> 14 weeks yields 3 months.
> 1 weeks yields 0 months.
>
If accuracy isnt a issue probably floor() could suite you:
months=select floor(weeks/4);
Regards,
Fernando.
from ta) subquery1
where c = 2;
This nesting is probably overhead in such a simple case as this, but in more
complex ones and specially with volatile functions it will provide an
improvement.
Regards,
Fernando.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
> -Mensaje original-
> De: Scott Marlowe [mailto:[EMAIL PROTECTED]
> Enviado el: Miércoles, 18 de Junio de 2008 17:47
> Para: Fernando Hevia
> >
> > For complex calculations I have obtained better performance using
> > nested queries. For example:
> > -Mensaje original-
> > De: Scott Marlowe [mailto:[EMAIL PROTECTED] Enviado el:
> > Miércoles, 18 de Junio de 2008 17:47
> > Para: Fernando Hevia
>
> > >
> > > For complex calculations I have obtained better performance using
> >
rrect? Is there another (better/simpler) way to achieve this?
Regards,
Fernando
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
quot; condition is satisfied. The DO INSTEAD part runs
an insert were minutes is NOT null so the rule should be ignored.
Where is the recursion then? I am on postgres 8.2.9.
Thanks for your hindsight Richard. I Will look into the trigger solution.
Still, I'd like to understand this recursion err
> -Mensaje original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] En nombre de Tom Lane
> Enviado el: Martes, 01 de Julio de 2008 19:24
> Para: Fernando Hevia
> CC: 'Richard Broersma'; pgsql-sql@postgresql.org
> Asunto: Re: [SQL] column default dep
pletes its execution within one second. Nevertheless in the LOG
duration entry it shows a statement duration of over 36 secs.
¿What is going on? ¿Where come those 36 seconds from?
Regards,
Fernando
--- Postgres Log extract ---
2008-07-22 15:52:37 ART|postgres| LOG: statement: select f_tasador();
> De: Richard Broersma [mailto:[EMAIL PROTECTED]
> Enviado el: Martes, 22 de Julio de 2008 17:19
>
> > 2008-07-22 15:52:37 ART|postgres| LOG: duration: 38.154 ms
>
> :o) You might be encountering a bit of parallax. This shows
> both 38 and 36 *milliseconds*.
>
That's embarrasing... I misto
s.
> Anyone have an example complete the how load and read each position of a
cursor?
> I wait your answer
> Thanks...guys
Hi Xavier. This is a very simple example of a function with an implicit
cursor.
Regards,
Fernando.
CREATE OR REPLACE FUNCTION f_cursor()
RETURNS void
> -Mensaje original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] En nombre de Gary Chambers
> Enviado el: Lunes, 01 de Septiembre de 2008 11:31
> Para: D'Arcy J.M. Cain
> CC: [EMAIL PROTECTED]; pgsql-sql@postgresql.org
> Asunto: Re: [SQL] MAY I HAVE YOUR ASSISTANCE
>
> >> I hav
You seem to be missing a ';' in this line:
v_from := c_from ;
v_where := p_where<--- missing ; here
v_stmt := c_select || v_from || v_where;
Regards,
Fernando
De: [EMAIL PROTECTED]
[mailto:[EMAIL
thing, like:
>
> (tbltransaksi.hargapokok * tbltransaksi.keluar)::numeric(2,0) AS modal
>
Consider that if you are NOT going to use the decimals you should really use
integer or bigint datatypes. The numeric type compute much slower than
integer datatypes.
Regards,
Fernando
--
Sent via pgsql-
tion.
Ref: http://www.postgresql.org/docs/current/static/datatype-numeric.html
The explicit "very slow" assertion scared me quite enough so to avoid
numeric types where possible.
Regards,
Fernando.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
table a
where date = (select max(b.date) from table b where b.key = a.key) q;
Regards,
Fernando.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
> -Mensaje original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] En nombre de Aarni Ruuhimäki
> Enviado el: Viernes, 10 de Octubre de 2008 07:56
> Para: pgsql-sql@postgresql.org
> Asunto: [SQL] SELECT multiple MAX(id)s ?
>
> Hello list,
>
> table diary_entry
>
> entry_id SERI
Hi Bart,
If you are using psql then you can get rid of those messages by entering on
the psql prompt:
\set verbose TERSE
If you are using pgAdmin, sadly there is currently no way for this
application to filter context messages.
Regards,
Fernando
_
De: [EMAIL PROTECTED] [mailto
the grouping by the #
> (ie, group by cat, then subsort on num, and select the "last"
> one of the group).
>
I wonder if this suites you:
SELECT sub.cat, t.id, sub.Num
FROM my_table t, ( SELECT cat, MAX(num) as Num FROM my_table GROUP_BY cat
) sub
WHERE t.cat = sub.cat AND t.Num = sub.Num
ORDER BY t.cat;
Regards,
Fernando.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
bject when it was created. Is
this information available on 8.3? Where should I look?
Thanks,
Fernando
> -Mensaje original-
> De: pgsql-sql-ow...@postgresql.org
> [mailto:pgsql-sql-ow...@postgresql.org] En nombre de Scott Marlowe
>
> On Mon, Dec 29, 2008 at 11:23 AM, Fernando Hevia
> wrote:
> > Hi list,
> >
> > I'm having a hard time trying
lpful.
>
> 2. you can also make some inference about the relative timing
> of object creation based on the OIDs (query
> pg_catalog.pg_proc rather than information_schema.routines
> for proc OIDs).
>
I am not sure this would be helpful since different databases are involved
(same produ
> -Mensaje original-
> De: Alvaro Herrera [mailto:alvhe...@commandprompt.com]
> Enviado el: Lunes, 29 de Diciembre de 2008 19:39
> Para: Fernando Hevia
> CC: 'George Pavlov'; pgsql-sql@postgresql.org
> Asunto: Re: [SQL] Object create date
>
> Fernando
> -Mensaje original-
> From: Mark Stosberg
>
> Hello,
>
> I use PostgreSQL and the "cube" type to perform geo-spatial
> zipcode proximity searches. I'm wondering about the best
> practices also supporting a geo-spatial distance search based
> on a city name rather than zipcode.
>
Hey everybody !!!
I am new on this list !!!
I have a little problem .
I try this on my system:
"select n_lote from pedidos except select rp.n_lote from relpedidos rp,
relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having
sum(rp.cantidad)=sum(rf.cantidad)"
I get this result:
Hey everybody !!!
I am new on this list !!!
I have a little problem .
I try this on my system: (Postgres 6.5.2, Linux)
"select n_lote from pedidos except select rp.n_lote from relpedidos rp,
relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having
sum(rp.cantidad)=sum(rf.cantida
Table 1
create table person (
doc text primary key,
etc
city text );
9 rows
create table sales (
doc text,
etc .
);
30 rows
select p.city,coun
#include
#include
char *fernando(char *texto)
{
char *resultp = palloc(strlen(texto)+5);
*resultp = *texto;
strcat(resultp," mais");
return resultp;
}
gcc -shared fernando.c -o fernando.so
CREATE FUNCTION fernando (bpchar) RETURNS bpchar
AS &
66 matches
Mail list logo
