[SQL] How to create Function which retruns username and password
Dear All, I want to create function which returns username and password from the database.I have users table in the database.in that i have username and password columns.I need return username and password using functions.it is urgent. I am waiting for your great response, Thanks, Regards, Ven
Re: [SQL] How to create Function which retruns username and password
In response to venkat :
> Dear All,
>
> I want to create function which returns username and password from the
> database.I have users table in the database.in that i have username and
> password columns.I need return username and password using functions.it is
> urgent. I am waiting for your great response,
test=# create table usernames (username text, passwort text);
CREATE TABLE
test=*# create or replace function get_usernames() returns setof usernames as
$$ begin return query select username, passwort from usernames; end;$$ language
plpgsql;
CREATE FUNCTION
test=*# insert into usernames values ('user1', 'password1');
INSERT 0 1
test=*# insert into usernames values ('user2', 'password2');
INSERT 0 1
test=*# select * from get_usernames();
username | passwort
--+---
user1| password1
user2| password2
(2 rows)
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Min and max element of an array column
Hello, is it possible to obtain the minimum and the maximum single element of an array column? Example: [1, 2 ,5] [3, -1, 6] [9, 18,-4 ] I'd just like to make a query that gives me the min (-4) and the max(18) of the column. Is that possible without a function? Thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Min and max element of an array column
In response to Gianvito Pio :
> Hello,
> is it possible to obtain the minimum and the maximum single element of
> an array column?
>
> Example:
> [1, 2 ,5]
> [3, -1, 6]
> [9, 18,-4 ]
>
> I'd just like to make a query that gives me the min (-4) and the max(18)
> of the column. Is that possible without a function? Thanks
test=*# select * from pio;
i
---
{1,2,5}
{3,-1,6}
{9,18,-4}
(3 rows)
test=*# select min(unnest), max(unnest) from (select unnest(i) from pio)
foo;
min | max
-+-
-4 | 18
(1 row)
It is 8.4, if you have a version < 8.4, you need the unnest-function:
CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT
LANGUAGE SQL AS $$SELECT $1[i] FROM
generate_series(array_lower($1,1),array_upper($1,1)) i;$$;
(with Thx to David Fetter)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Question
Hi for all,
What is the possible sources of the SQLState 55000 "OBJECT NOT IN
PREREQUISITE STATE" ?
The error 55000 was triggered when I executed the following query :
"select currval('"BatchTreatment_batch_treatment_id_seq"');"
Thanks in advance.
Cordially.
Re: [SQL] Question
Call nextval first?
Too many quotes?
aymen marouani wrote:
Hi for all,
What is the possible sources of the SQLState 55000 "OBJECT NOT IN
PREREQUISITE STATE" ?
The error 55000 was triggered when I executed the following query :
"select currval('"BatchTreatment_batch_treatment_id_seq"');"
Thanks in advance.
Cordially.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Question
On Wed, Sep 02, 2009 at 04:28:34PM +0200, aymen marouani wrote:
> Hi for all,
> What is the possible sources of the SQLState 55000 "OBJECT NOT IN
> PREREQUISITE STATE" ?
> The error 55000 was triggered when I executed the following query :
> "select currval('"BatchTreatment_batch_treatment_id_seq"');"
If you execute that (or a similar command) from psql, you'll get a more
human friendly error message:
repository=# select currval('moduleid_seq');
ERROR: currval of sequence "moduleid_seq" is not yet defined in this
session
Checking the docs at
http://www.postgresql.org/docs/8.3/interactive/functions-sequence.html
currval
Return the value most recently obtained by nextval for this sequence
in the current session. (An error is reported if nextval has never
been called for this sequence in this session.) Notice that because
this is returning a session-local value, it gives a predictable
answer whether or not other sessions have executed nextval since the
current session did.
So, that sequence has not had 'nextval' called on it in this session.
Ross
--
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer & Admin, Research Scientistphone: 713-348-6166
The Connexions Project http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PostgreSQL Function: how to know the number of the returned results of the Query
count aggregate function can be use like this. "SELECT count(rate) into row_count from voiptariff where id= 9;" CREATE OR REPLACE FUNCTION get_credit_time(text, text) RETURNS integer AS $BODY$ DECLARE row_count integer; BEGIN rate numberic(9,4); SELECT count(rate) into row_count from voiptariff where id= 9; IF row_count =1 THEN -- As example, but I am asking how to do it? . ELSE . END IF END $BODY$ LANGUAGE 'sql' IMMUTABLE COST 100; ALTER FUNCTION get_bool(text) OWNER TO gkradius; - Original Message - From: "bilal ghayyad" To: Sent: Tuesday, September 01, 2009 3:53 AM Subject: [SQL] PostgreSQL Function: how to know the number of the returned results of the Query Hi List; I am building a function in the SQL (PostgreSQL), so I will be able to call this function using a SELECT query. Inside the body of this function, I was need to do the following but not able to know how: I have a SELECT statement (inside the function script itself), and I need to know the number of the returned rows of that select query, if it is one row or 2 or 3 , ... How? In which paramter I will be able to check this? For example, I have the following function: CREATE OR REPLACE FUNCTION get_credit_time(text, text) RETURNS integer AS $BODY$ DECLARE BEGIN rate numberic(9,4); SELECT rate from voiptariff where id= 9; IF num_rows ==1 THEN -- As example, but I am asking how to do it? . ELSE . END IF END $BODY$ LANGUAGE 'sql' IMMUTABLE COST 100; ALTER FUNCTION get_bool(text) OWNER TO gkradius; In this function, I need to check the number of returned rows of the statement: SELECT rate from voiptariff where id= 9; because based on it I am going to build if statment, How? Any help? Regards Bilal -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Question
Hi for all,
What is the possible sources of the SQLState 55000 "OBJECT NOT IN
PREREQUISITE STATE" ?
The error 55000 was triggered when I executed the following query :
"select currval('"BatchTreatment_batch_treatment_id_seq"');"
Thanks in advance.
Cordially.
Re: [SQL] Odd sort behaviour
Rob Sargent wrote: I'm sure this a life-time's worth of discussion on the merits of treating "." as nothing when sorting Well, every sorted reference work in society at large seems to have a different idea of how to sort - just compare the phone book to the dictionary. That's the point of locales, to formalize such rules so that you can coerce your system to follow one or another set as needed. That way you don't have to agree or disagree with any rule, such as ignoring punctuation in the sort, simply be aware of whether it applies to any given situation. -- Lew -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
