Re: [SQL] record type
2008/7/10 Marcin Krawczyk <[EMAIL PROTECTED]>:
> Hi. I need to know whether it's possible for a plpgsql function to accept
> record type parameters ? Is there a way to accomplish that ?
> I need to use something like ('1','2','3') as a parameter.
>
> regards
> mk
>
All about record type
http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
section 38.3.4
but, try this ;-)
merlin=# create table your_table(col1 int, col2 varchar(12), col3 int);
CREATE TABLE
merlin=# create or replace function test_1(val_of your_table) returns void
as $$
declare
begin
insert into your_table values(val_of.col1, val_of.col2, val_of.col3);
end;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE FUNCTION
merlin=# select test_1((1,'test',2));
test_1
(1 row)
merlin=# select * from your_table ;
col1 | col2 | col3
--+--+--
1 | test | 2
(1 row)
Time: 0.380 ms
--
--
Serdecznie pozdrawiam
Pawel Socha
[EMAIL PROTECTED]
programista/administrator
perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{
a%%s%%$_%ee'
Re: [SQL] postgres time zone settings(time difference in Server and client)
2008/7/25 Anoop G <[EMAIL PROTECTED]>:
>
> Hai all,
>
>I have database running on server. I am using python to run my
> application.postgres client is running on the clients .All clients are
> connected to a single database running on server. Each time application
> starts ,the login time is taken from server using the query
> """select CAST ( timeofday() AS timestamp"""
>
> But the problem the time which I got is different from that of server
> machine . I Think the postgres client is adding some values to the server
> time. How can I solve this .Is there any configuration setting for client to
> solve this?.In which location client's configuration file is stored( I am
> using Linux(Debian )?.
>
> here the result I got:
>
> The correct time in server: 2008-07-25 14:16:54
>
> But What i got from client (with database cursor of sever database):
>2008-07-25 19:46:33.46 (difference of more than 5 Hr ) Where this
> difference come?
>
> any one know the answer pls help me.
>
> Thanks in advance
>
> Anoop
>
>
>
>
server localtime is correct ?
/etc/localtime ...
--
--
Serdecznie pozdrawiam
Pawel Socha
[EMAIL PROTECTED]
programista/administrator
perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{
a%%s%%$_%ee'
Re: [SQL] regexp_replace
2008/8/1 Marcin Krawczyk <[EMAIL PROTECTED]>
> Hi all. I'd like to know whether it's possible to reverse the
> behaviour of regexp_replace, meaning :
> now if I do
> SELECT regexp_replace ('foobarbaz', 'b..', 'X') I get 'fooXbaz' - it
> replaces the string that matches given pattern with 'X', how do I
> achieve the opposite - replace the string that doesn't match the
> pattern ?
>
> regards
> mk
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
merlin=# SELECT regexp_replace ('foobarbaz', '[^b]', 'X', 'g');
regexp_replace
XXXbXXbXX
(1 row)
--
--
Serdecznie pozdrawiam
Pawel Socha
[EMAIL PROTECTED]
programista/administrator
perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{
a%%s%%$_%ee'
Re: [SQL] return setof record - strange behavior
2008/8/4 Marcin Krawczyk <[EMAIL PROTECTED]> > Hi everybody. Can anyone enlighten me what's wrong with this function : > > CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer, OUT > ro integer, OUT mi integer) > RETURNS SETOF record AS > $BODY$ > DECLARE > w record; > cy integer := EXTRACT (YEAR FROM current_date); > > BEGIN > > FOR w IN >SELECT (CASE WHEN m > 12 THEN cy + 1 ELSE cy END)::integer, (CASE > WHEN m > 12 THEN m - 12 ELSE m END)::integer >FROM generate_series(mon + 1, mon + intv) AS m > LOOP >RETURN next; > END LOOP; > > END; > > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > > SELECT * FROM month_year(10, 5); > > Why does it return empty SET ? The amount of rows is correct though > I'm running 8.1.4 > > regards > mk > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > Hi merlin=# CREATE OR REPLACE FUNCTION month_year(mon integer, intv integer) RETURNS SETOF record AS $BODY$ DECLARE w record; cy integer := EXTRACT (YEAR FROM current_date); BEGIN FOR w IN SELECT (CASE WHEN m > 12 THEN cy + 1 ELSE cy END)::integer, (CASE WHEN m > 12 THEN m - 12 ELSE m END)::integer FROM generate_series(mon + 1, mon + intv) AS m LOOP RETURN next w; END LOOP; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; and merlin=# SELECT * FROM month_year(10, 5) as (x integer, y integer); x | y --+ 2008 | 11 2008 | 12 2009 | 1 2009 | 2 2009 | 3 (5 rows) without output params -- -- Serdecznie pozdrawiam Pawel Socha [EMAIL PROTECTED] programista/administrator perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{ a%%s%%$_%ee'
Re: [SQL] EXECUTE query INTO problem
2008/11/25 Tk421 <[EMAIL PROTECTED]>: > I've got a problem with a function: It receives two parameters, the first, > the table name, and the second, a where condition. The function looks like > this: > > DECLARE > cod bigint; > query TEXT; > > BEGIN > query = 'SELECT codigo FROM ' || $1 || ' WHERE ' || $2; > > EXECUTE query INTO cod; > · > · > · > END; > > I've alwais get the same error, in the EXECUTE sentence: it says: Error at > or near NULL at character X > > I've also tried declaring cod as row, but the error is the same. > > Anybody can help me? > > Thank you very much > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > CREATE OR REPLACE FUNCTION test(character varying, character varying) RETURNS integer AS $BODY$declare r_int int; q varchar; begin if $1 is not null and $2 is not null then q = 'select p1 from '||$1||' where '||$2; execute q into r_int; return r_int; else return null; end if; end;$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION test(character varying, character varying) OWNER TO merlin; and simple table ;] merlin=> \d t1 Table "public.t1" Column | Type | Modifiers +---+--- p1 | integer | p2 | character varying(32) | merlin=> insert into t1 values(2, 'abc');INSERT 0 1 merlin=> select test('t1', ' p2= ''abc''') ; test -- 2 (1 row) merlin=> select test(null, ' p2= ''abc''') ; test -- (1 row) And all its works -- Serdecznie pozdrawiam Pawel Socha [EMAIL PROTECTED] programista/administrator perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-{ a%%s%%$_%ee' -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Comparing two tables of different database
2009/4/29 Nicholas I : > Hi, > > can anybody me suggest me, how to compare two tables of different > database. > > -Nicholas I > what you mean 'different database' :) diffrent version, 2 instance ? Maybe this help: http://www.postgresql.org/docs/current/static/dblink.html -- Pawel Socha -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SUM Array values query
2009/5/18 Dani Castaños :
> Hi all,
>
> I've this query including arrays:
>
> SELECT hour[1:5], statistics_date
> FROM statistics_daily
> WHERE statistics_date = to_date( '03/01/2008', 'DD/MM/' )
>
> Result:
>
> hour | statistics_date
> +-
> {1800,1800,1800,1800,1800} | 2008-01-03
>
>
> I'm just wondering if there's some way to retrieve the hour column as the
> sum of the array values... Just like this:
>
> hour | statistics_date
> +-
> 9000 | 2008-01-03
>
>
> Hour is a bigint[] array column.
> My version of postreSQL is 8.1.9
>
> Thank you in advanced!
>
> P.S.: Sorry if I had send it before, but I think I was not subscribed to the
> mailist.
>
> --
> Dani Castaños Sánchez
> [email protected]
>
>
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Create function in pure sql and ...
,-[15:27:18]mer...@[local]:5432=
`-merlin>create function array_sum (bigint[]) returns bigint as $$
SELECT sum($1[i])::bigint FROM
generate_series(array_lower($1,1),array_upper($1,1)
) index(i); $$ language sql;
CREATE FUNCTION
Time: 16,203 ms
,-[15:28:02]mer...@[local]:5432=
`-merlin>select array_sum(col_array) from t30;
array_sum
---
9000
(1 row)
:)
--
Pawel Socha
[email protected]
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
