Обсуждение: stored procedures in postgresql user plpgsql
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I know that there are not stored procedures like that in oracle. I know that you have to create functions.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I have tried to create a simple function to select data from a table. Could you all please correct me</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Thanks</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> CREATE FUNCTION retrive_atype(varchar) RETURNS varchar AS '</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> BEGIN</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> RETURN select username from masteraccount where atype=$1;</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> END;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> ' LANGUAGE 'plpgsql';</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">To call the function I used </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Select retrive_atype();</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Please help</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Comic Sans MS" size="3"><span style="font-size: 12.0pt;font-family:"Comic Sans MS"">Robert Stewart</span></font><p class="MsoNormal"><font face="Comic Sans MS" size="3"><spanstyle="font-size: 12.0pt;font-family:"Comic Sans MS"">Network Eng</span></font><p class="MsoNormal"><font face="Comic Sans MS" size="3"><spanstyle="font-size: 12.0pt;font-family:"Comic Sans MS"">Commonwealth Office of Technology</span></font><p class="MsoNormal"><font face="ComicSans MS" size="3"><span style="font-size: 12.0pt;font-family:"Comic Sans MS"">Finance and Administration Cabinet</span></font><p class="MsoNormal"><font face="ComicSans MS" size="3"><span style="font-size: 12.0pt;font-family:"Comic Sans MS"">101 Cold Harbor</span></font><p class="MsoNormal"><font face="Comic Sans MS" size="3"><spanstyle="font-size: 12.0pt;font-family:"Comic Sans MS"">Work # 502 564 9696</span></font><p class="MsoNormal"><font face="Comic Sans MS" size="3"><spanstyle="font-size: 12.0pt;font-family:"Comic Sans MS"">Cell # 502 330 5991</span></font><p class="MsoNormal"><font face="Comic Sans MS" size="3"><spanstyle="font-size: 12.0pt;font-family:"Comic Sans MS"">Email <a href="mailto:RobertD.Stewart@ky.gov">RobertD.Stewart@ky.gov</a></span></font><pclass="MsoNormal"><font face="Times New Roman"size="3"><span style="font-size: 12.0pt"> </span></font></div>
On Mon, Nov 29, 2004 at 01:37:42PM -0500, RobertD.Stewart@ky.gov wrote:
> I have tried to create a simple function to select data from a table. Could
> you all please correct me
It would be helpful if you told us what you want to happen and what
actually does happen. Without that information we have to guess
at your intentions.
> CREATE FUNCTION retrive_atype(varchar) RETURNS varchar AS '
> BEGIN
> RETURN select username from masteraccount where atype=$1;
> END;
> ' LANGUAGE 'plpgsql';
>
> To call the function I used
>
> Select retrive_atype();
You've defined the function to take a VARCHAR argument but you
called it without an argument. Also, you've defined it to return
a single VARCHAR value, but if the query could return multiple
values then the function should return SETOF VARCHAR. Maybe this
is closer to what you need:
CREATE FUNCTION retrive_atype(VARCHAR) RETURNS SETOF VARCHAR AS '
DECLARE rec RECORD;
BEGIN FOR rec IN SELECT username FROM masteraccount WHERE atype = $1 LOOP RETURN NEXT rec.username; END
LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
You'd call the function like this:
SELECT * FROM retrive_atype('some-atype-value');
If that's not what you're looking for then please provide more details.
BTW, is "retrive" supposed to be "retrieve"?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Please Cc the mailing list when you respond -- that way others can participate in and learn from the discussion. Also, if you have a new question then please start a new thread with a relevant Subject header. On Mon, Nov 29, 2004 at 10:12:59PM -0500, RobertD.Stewart@ky.gov wrote: > Thanks for the help > > That is what I needed > > Do you have any examples of insert statements for multiple variable > > I'm trying to create a web page that would call these functions See the documentation for the INSERT statement: http://www.postgresql.org/docs/7.4/static/sql-insert.html If you're having trouble with something, then please describe what you're trying to do and the difficulties you're having. It's helpful if you post the SQL statements you're executing along with a description of what you want to happen and what actually does happen. For the latter, including the output from a psql session can be useful. -- Michael Fuhr http://www.fuhr.org/~mfuhr/