It was exactly what I was looking for. Now if possible please show us how to call that Procedure from a Windows forms using C# code. Iam suscribed to your channel and soon I will check every video you have to learn much more from you.
@@Knowledge360Channel hey dude. Show us how to make a backup from a windows form event button or method using postgres and how we can share that database over Lan please. It's all I ask in this life 😐
Nice very informative. I have one question can we use this to make user defined tables and can we use this method to fetch all the tables inside database.?
Hi Akram, Can you please explain how to call a stored procedure from another stored procedure in postgreSQL? Tried with "call" , but it is giving error. Thanks in advance.
here is my question.i am creating a procedure but error comes NOTICE: table "tempunitsbooked" does not exist, skipping ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function degams.sp_report_unitsbnastatistics_alloted_details(integer,integer,timestamp without time zone,numeric,text) line 21 at SQL statement SQL state: 42601 procedure is ------- CREATE OR REPLACE PROCEDURE degams.sp_report_unitsbnastatistics_alloted_details( u_ga_id integer, u_ba_id integer, u_date timestamp without time zone, INOUT rtn_unit_type numeric, INOUT rtn_unit_desc text) LANGUAGE 'plpgsql' AS $BODY$ Declare no_of_rooms_Available int; --begin try begin --set no_of_rooms_available= select sum(no_units_vacant) into no_of_rooms_available from degams.getvacantunitcount where ga_id=u_ga_id and ba_id=u_ba_id and dateval= u_Date; if no_of_rooms_Available=0 then
select count(a.unit_type_id) vacant,unit_type_m.unit_type_desc as type from degams.GUEST_ACCOM_UNIT_M a , degams.unit_type_m where WHETHER_ONLINE = 'Y' and unit_quota'EQ' and ga_id=u_ga_id and ba_id=u_ba_id and a.unit_type_id=unit_type_m.unit_type_id group by unit_type_m.unit_type_desc having count(*)>=1; rtn_unit_type := vacant; rtn_unit_desc := type;
else
select count(a.unit_type_id) vacant,unit_type_m.unit_type_desc as type from degams.GUEST_ACCOM_UNIT_M a , degams.unit_type_m where WHETHER_ONLINE = 'Y' and unit_quota'EQ' and ga_id=u_ga_id and ba_id=u_ba_id and a.unit_type_id=unit_type_m.unit_type_id and exists(select br_id from degams.allotments_vu where u_Date< booking_upto and u_Date >=booking_from and unit_id=a.unit_id and ga_id=u_ga_id) group by unit_type_m.unit_type_desc having count(*)>=1; end if; get diagnostics no_of_rooms_Available =row_count; rtn_unit_type := vacant; rtn_unit_desc := type;
I have created it, didn't get any error. Will you connect to me via mail to solve the issue at your side? Here is my mail knowledge.360.knowledge@gmail.om
Sorry bro, That's not possible, we need to do it through function only. If you want to return a table, create a stored function and return table as I have done in previous video.
sorry btw, i wanted to ask you, how could i do if i need to write a script that can create a db and its tables?? the only thing im getting is that you cant create a db inside a function or transaction, so i dont know how to do this@@Knowledge360Channel
You're welcome 🤗... Actually it's not necessary to be public.tablename. It depends on grants, owner and all...for demonstration I have used public schema here.
*Please help me get that beautiful RUclips Silver button. Do subscribe to the channel if my video was helpful.* ❤
*Only 3.9% of viewers are subscribing to my channel.* 😓
*Please Like, Comment, and Subscribe to my channel.* ❤
Very easy to understand. Kudos to you
You're welcome 🤗
@@Knowledge360Channel do you have other video of store procedure
@@julichinwakamoto Yes, please visit my channel page, you will get plenty of examples, also browse through playlists.
*Please help me get that beautiful RUclips Silver button. Do subscribe to the channel if my video was helpful.* ❤
Wow very well done and well explained.
Thanks brother
This one helped me a lot.
Thanks, nice explanation
You're welcome 😊..plz subscribe to my channel to support me
Much helpful video.
It was exactly what I was looking for. Now if possible please show us how to call that Procedure from a Windows forms using C# code. Iam suscribed to your channel and soon I will check every video you have to learn much more from you.
As soon as possible
@@Knowledge360Channel hey dude. Show us how to make a backup from a windows form event button or method using postgres and how we can share that database over Lan please. It's all I ask in this life 😐
thank you
You're welcome 😊... please subscribe my channel to get updates.
Nice very informative.
I have one question can we use this to make user defined tables and can we use this method to fetch all the tables inside database.?
Yes we can
Very clear. Thank you
Hi! It was really helpful. Thanks a lot
wow...amazing
good job, Thank you
Great video 📹👍
Akram can we return query or use return in store procedure like in function
Great
good one
Very helpful.. If possible give some more example.. Thanks
Hi, I do not understand the part where you write VALUES($1,$2) - why did you use $1 and $2? What does this mean?
It's a great feature of mentioning the parameter position instead of writing the parameter names.
In place of that, I can also write the parameter names.
@@Knowledge360Channel does pgsql support parameter names in procedure like sql server or oracle ?
Yes, it works the same as other databases PLSQL languages
@@hendrasdrtube Here's an example of that you are asking.
ruclips.net/video/zUFgqxQYmFI/видео.html
Why is it so easy but our professor gives the most complex exercises, given that we're total beginners? 😭
Ask your professor to follow my channel. 😂😂😂😂
Thanks for the appreciation 😊❤️
*Please help me get that beautiful RUclips Silver button. Do subscribe to the channel if my video was helpful.* ❤
Subscribe My Channel to get the updates of the videos, or visit the PostgreSQL playlist to learn more on PostgreSQL.
where we can check store procedures in left side menu
Browse to Databases/DatabaseName/Schemas/YourSchemaName/Procedures
Akram can you come up with new videos on trigger , cursor indexes with examples
Yes...from 1st week of May
Table valued parameters to procedure with (Try- Catch) and error Handle in PostgreSQL
How can I create sp in Postgres to truncate 8 or more tables every day so that we can insert new data? Also, few tables have FKs
You can use dynamic SQL here
Hi Akram,
Can you please explain how to call a stored procedure from another stored procedure in postgreSQL? Tried with "call" , but it is giving error.
Thanks in advance.
Just call like this,
NewProc();
It will execute the procedure.
@@Knowledge360Channel syntax error when did this. It is looking for the keyword "call"..
@@gauriagarkhedkar5611 Okay, I will check and let you know. Can you please share the procedure on my email?
@@Knowledge360Channel sure. Thanks for responding.
I have just uploaded a video for this issue. Please check this out.
ruclips.net/video/jAjQDRunt5U/видео.html
please upload your video with higher voice
here is my question.i am creating a procedure but error comes NOTICE: table "tempunitsbooked" does not exist, skipping
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function degams.sp_report_unitsbnastatistics_alloted_details(integer,integer,timestamp without time zone,numeric,text) line 21 at SQL statement
SQL state: 42601
procedure is -------
CREATE OR REPLACE PROCEDURE degams.sp_report_unitsbnastatistics_alloted_details(
u_ga_id integer,
u_ba_id integer,
u_date timestamp without time zone,
INOUT rtn_unit_type numeric,
INOUT rtn_unit_desc text)
LANGUAGE 'plpgsql'
AS $BODY$
Declare no_of_rooms_Available int;
--begin try
begin
--set no_of_rooms_available=
select sum(no_units_vacant) into no_of_rooms_available from degams.getvacantunitcount
where ga_id=u_ga_id and ba_id=u_ba_id and dateval= u_Date;
if no_of_rooms_Available=0 then
select count(a.unit_type_id) vacant,unit_type_m.unit_type_desc as type
from degams.GUEST_ACCOM_UNIT_M a ,
degams.unit_type_m where WHETHER_ONLINE = 'Y' and unit_quota'EQ' and ga_id=u_ga_id
and ba_id=u_ba_id
and a.unit_type_id=unit_type_m.unit_type_id
group by unit_type_m.unit_type_desc having count(*)>=1;
rtn_unit_type := vacant;
rtn_unit_desc := type;
else
select count(a.unit_type_id) vacant,unit_type_m.unit_type_desc as type from
degams.GUEST_ACCOM_UNIT_M a ,
degams.unit_type_m
where WHETHER_ONLINE = 'Y' and unit_quota'EQ' and ga_id=u_ga_id and ba_id=u_ba_id
and a.unit_type_id=unit_type_m.unit_type_id and exists(select br_id
from degams.allotments_vu
where u_Date< booking_upto and u_Date >=booking_from and unit_id=a.unit_id and ga_id=u_ga_id)
group by unit_type_m.unit_type_desc having count(*)>=1;
end if;
get diagnostics no_of_rooms_Available =row_count;
rtn_unit_type := vacant;
rtn_unit_desc := type;
end ;
$BODY$;
Okay I will check and resend you
I have created it, didn't get any error. Will you connect to me via mail to solve the issue at your side? Here is my mail knowledge.360.knowledge@gmail.om
@@Knowledge360Channel procedure created smoothly but did'nt get result from it
Will you show me how you are calling it?
@@Knowledge360Channel call degams.sp_report_unitsbnastatistics_alloted_details('13','1','2020-10-05',NULL,NULL)
How to create procedures for select querry
Will make a video on it. Subscribe the channel to get updates.
*Please help me get that beautiful RUclips Silver button. Do subscribe to the channel if my video was helpful.* ❤
Hi, can you plz explain postgresql store procedure for select record from a table
Okay I will explain it. Thank you bro
Sorry bro, That's not possible, we need to do it through function only. If you want to return a table, create a stored function and return table as I have done in previous video.
does postgresql 9.6.10 supports procedure
You can check that from postgresql docs
Спасибо МУЖИК
how to call select statement in store procedure.?
for example: select * from Table1
I am planning to make video on it.
@@Knowledge360Channel ok thank you..mean while just give idea how to call.. because tomorrow I want to create it.
Search for SELECT INTO statement in PostgreSQL Procedure. I hope you will get it.
@@Knowledge360Channel ok let me check
@@Knowledge360Channel thanks
whats plpsql?
PLpgSQL is the procedural language of PostgreSQL, just like PLSQL of Oracle
oh ty! @@Knowledge360Channel
sorry btw, i wanted to ask you, how could i do if i need to write a script that can create a db and its tables?? the only thing im getting is that you cant create a db inside a function or transaction, so i dont know how to do this@@Knowledge360Channel
*Please help me get that beautiful RUclips Silver button. Do subscribe to the channel if my video was helpful.* ❤
If else in stored procedure
I didn't get your question
@@Knowledge360Channel how to use if else in sp and also how about return value
Okay, I will make a video on this within a day. Please subscribe to get the notification
@@Knowledge360Channel is there upsert option in postgres stored procedure... i need to select and insert into table in same stored procedure
damn you have to use public.the_name_of_your_table otherwise it won't work i get it now thanks so much
You're welcome 🤗... Actually it's not necessary to be public.tablename. It depends on grants, owner and all...for demonstration I have used public schema here.
Thank you
Great